Error 13 Type Mismatch (1 Viewer)

Drand

Registered User.
Local time
Today, 18:40
Joined
Jun 8, 2019
Messages
65
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.

Code:
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

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 01:40
Joined
Feb 28, 2001
Messages
18,347
Just as a test, do this inside your event code:

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

Registered User.
Local time
Today, 18:40
Joined
Jun 8, 2019
Messages
65
Thanks for the response. It is showing 12:00:00am
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:40
Joined
Oct 29, 2018
Messages
13,202
Hi. Just curious, which line is actually causing the error?
 

Drand

Registered User.
Local time
Today, 18:40
Joined
Jun 8, 2019
Messages
65
It is the line
StockNo = DLookup("StockNumber", "tblStock", "StockItem =" & "'" & [StockItem] & "'" _
And "[Dateadded]= #" & [MyLatestDate] & "#")
 

June7

AWF VIP
Local time
Yesterday, 22:40
Joined
Mar 9, 2014
Messages
3,362
The AND operator must be within quote marks because it is literal text:

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

Drand

Registered User.
Local time
Today, 18:40
Joined
Jun 8, 2019
Messages
65
Thank you all. Quite right June7, this fixed it for me. Much appreciated
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 01:40
Joined
Feb 28, 2001
Messages
18,347
Thanks for the response. It is showing 12:00:00am

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

Users who are viewing this thread

Top Bottom