Error 13 Type Mismatch

[Drand]

Hi folks,

I am sure this really simple but I just can't seem to find what is wrong here.

The following code is creating an error 13 message. Strangely though, variables MyLatestDate and StockItem are returning the correct values. It is only the Dlookup where I am struggling.

Private Sub StockItem_AfterUpdate()
Dim MyLatestDate As Date
Dim StockItem As String
Dim StockNo As Integer

StockItem = Me.StockItem

MyLatestDate = DLookup("MaxofDateAdded", "QryLatestDateStockAdded", "MaxofDateAdded = #" _
 & DMax("MaxofDateAdded", "QryLatestDateStockAdded") & "#")


StockNo = DLookup("StockNumber", "tblStock", "StockItem =" & "'" & [StockItem] & "'" _
And "[Dateadded]= #" & [MyLatestDate] & "#")


End Sub

Would appreciate any assistance.

Many thanks
David

 

[The_Doc_Man]

Just as a test, do this inside your event code:

StockItem = Me.StockItem

MyLatestDate = DMax( "MaxofDateAdded", "QryLatestDateStockAdded" )

MyLatestDate = DLookup("MaxofDateAdded", "QryLatestDateStockAdded", "MaxofDateAdded = #" _
 & MyLatestDate & "#")

StockNo = DLookup("StockNumber", "tblStock", "StockItem =" & "'" & [StockItem] & "'" _
And "[Dateadded]= #" & [MyLatestDate] & "#")

Using debugging, put your break on the first statement and Debug.Print MyLatestDate. Single step and hover the cursor over MyLatestDate in the Criteria of the DLookup. I'm curious to see what it tells you. I suspect a date formatting issue and splitting that DMax out of the DLookup would settle the doubt by letting you see what is coming out of the routine.

 

[Drand]

Thanks for the response. It is showing 12:00:00am

 

[theDBguy]

Hi. Just curious, which line is actually causing the error?

 

[Drand]

It is the line
StockNo = DLookup("StockNumber", "tblStock", "StockItem =" & "'" & [StockItem] & "'" _
And "[Dateadded]= #" & [MyLatestDate] & "#")

 

[June7]

The AND operator must be within quote marks because it is literal text:

StockNo = DLookup("StockNumber", "tblStock", "StockItem ='" & [StockItem] & "' AND [Dateadded]= #" & [MyLatestDate] & "#")

 

[Drand]

Thank you all. Quite right June7, this fixed it for me. Much appreciated

 

[The_Doc_Man]

Thanks for the response. It is showing 12:00:00am

FYI - since it didn't give you a date with that, it returned 0.