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

Question

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.

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

Replace belongs to Visual Basic for Applications. Latest DLL is VBE7.DLL. — Gustav
– Gustav, Commented May 1, 2018 at 11:44
How did you define db? Please add the code with initialization too — Siyon DP
– Siyon DP, Commented May 1, 2018 at 11:58
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
– Erik A, Commented May 1, 2018 at 12:43
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
– Bob77, Commented May 1, 2018 at 22:15
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.
– Luke G., Commented May 2, 2018 at 13:39

dbmitch · Accepted Answer · 2018-05-08 16:11:03Z

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") & "#)

edited May 8, 2018 at 16:11

answered May 2, 2018 at 18:16

dbmitch

5,4064 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kelum Srimal Over a year ago

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 Over a year ago

I added data samples of InvoiceDate Field in my edited question

dbmitch Over a year ago

Okay - changed the solution to match the fact it is not a date field. Just strip time from the full field before comparing

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related