1

I'm working with a vb 6.0 application. There I'm using a replace function in my query. The query works fine in access but when it runs in vb it gives me the error:

"Undefined function 'Replace' in expression. Run-time error 3085".

I think that I'm missing references. I currently added following references.

References

The 'Replace' is located in the where clause:

...WHERE ((DateValue(Replace(Invoice.InvoiceDate, ': ', ' ')) Between...

The following full query is giving me the error.

Set DrsInv = db.OpenRecordset("SELECT Invoice.InvoiceDate, InvoicedProduct.InvoiceType, Invoice.InvoiceStatus, Invoice.RetailerID, Invoice.DailySalesID, Invoice.RepID, InvoicedProduct.Quantity, InvoicedProduct.UnitRate, InvoicedProduct.TotalItemValue From Invoice INNER JOIN InvoicedProduct ON (Invoice.DailySalesID = InvoicedProduct.DailySalesID) AND (Invoice.RepID = InvoicedProduct.RepID) AND (Invoice.InvoiceID = InvoicedProduct.InvoiceID)" _
                            & " WHERE ((DateValue(Replace(Invoice.InvoiceDate, ': ', ' ')) Between #" & Format(dtpFrom.value, "yyyy/mm/dd") & "# And #" & Format(dtpTo.value, "yyyy/mm/dd") & "#) AND (InvoicedProduct.ProductID = '" & Srs("ProductID") & "'))" _
                            & " GROUP BY Invoice.InvoiceDate, InvoicedProduct.InvoiceType, Invoice.InvoiceStatus, Invoice.RetailerID, Invoice.DailySalesID, Invoice.RepID, InvoicedProduct.Quantity, InvoicedProduct.UnitRate, InvoicedProduct.TotalItemValue HAVING ((InvoicedProduct.InvoiceType)='Invoice' AND (Invoice.InvoiceStatus)='VALID') Order By Invoice.InvoiceDate DESC")

I use the replace function because InvoiceDate is a text feild. So it is giving me the error "Data type mismatch", when I try to convert it to a date field in query. The colon inside the date is the reason for this error. That's why I need to replace the colon. I could not be able to change the database field. So this is the solution as the way I see it.

The data of the field InvoiceDate looks like following

enter image description here enter image description here

Kelum Srimal
  • 159
  • 1
  • 3
  • 17
  • 1
    Replace belongs to _Visual Basic for Applications_. Latest DLL is _VBE7.DLL_. – Gustav May 01 '18 at 11:44
  • 1
    How did you define `db`? Please add the code with initialization too – Siyon DP May 01 '18 at 11:58
  • 1
    Looks like the problem is `Replace` isn't recognized by the database engine. I couldn't replicate it, but I do know `Replace` is at an odd place, where it is a standard function, but isn't allowed in default values, for example. – Erik A May 01 '18 at 12:43
  • 5
    The Jet Expression Service doesn't implement Replace, InStrRev, or several other newer functions. MS Access uses a proprietary scheme to inject its own VBA processor in its place but VB6 programs can't do that. – Bob77 May 01 '18 at 22:15
  • TSion Sir I use "Dim db As Database". Should I use ADODB or DAO. Like Dim db As ADODB Database. Gustav, do I need to get the latest dll. – Kelum Srimal May 02 '18 at 04:02
  • I tried Dim db As DAO.Database. But the same error – Kelum Srimal May 02 '18 at 04:11
  • I think Bob nailed it – StayOnTarget May 02 '18 at 11:11
  • 1
    Why do you even need to do this Replace() in the first place? – StayOnTarget May 02 '18 at 11:12
  • 2
    Can you just build the format you want from the InvoiceDate field by using lefts, rights, etc and concatenating them together? It would help to see what some examples of InvoiceDate values look like in your system. – Luke G. May 02 '18 at 13:39
  • 1
    This is the same reason that a VB6 program cannot use "user defined functions" in a Jet query expression. I've never found a workaround. – Bob77 May 02 '18 at 15:35
  • 1
    [This thread](https://stackoverflow.com/questions/24908724), from someone who is having the same problem in C#, has some ideas that may be of some use. – BobRodes May 02 '18 at 16:17
  • I edited the question mentioning why I use replace function. – Kelum Srimal May 08 '18 at 07:44
  • Luke G. Sir, I have to try it. but I'm no expert. But it's worth to try. Thanks. – Kelum Srimal May 08 '18 at 07:45

1 Answers1

0

This function doesn't make much sense to me. As the commenters have asked can you explain why you need to remove the ":" from InvoiceDate? As @LukeG says - adding data examples would help us.

By removing the ":" from the time AM/PM part of the date, your function will fail even if the Replace worked, because you'd be asking DateValue to convert as string that is now not formatted as a proper date

There's a couple things you can do:

EDITED:

Since we now know InvoiceDate is NOT a Date Field you can just remove the time part of the field by taking the first 10 characters and converting to date:

(CDate(Left(Invoice.InvoiceDate,10)) Between #" & Format(dtpFrom.value, "yyyy/mm/dd") & "# And #" & Format(dtpTo.value, "yyyy/mm/dd") & "#)
dbmitch
  • 5,361
  • 4
  • 24
  • 38
  • It is my bad not giving the all info of my question sir. The InvoiceDate is not a Date field. And therefore it need to convert and the second answer is giving me the error data type mismatch – Kelum Srimal May 08 '18 at 07:48
  • I added data samples of InvoiceDate Field in my edited question – Kelum Srimal May 08 '18 at 09:13
  • Okay - changed the solution to match the fact it is not a date field. Just strip time from the full field before comparing – dbmitch May 08 '18 at 16:11