Runtime error '3464': Data type mismatch in criteria expression

[mamradzelvy]

Hi,
I'm getting said error when trying to navigate to a record within a form based on matching dates, i don't understand why though, as I have the date format displaying the same way on both forms and same for the table where the records are saved.
Any tips?

Private Sub lstInventory_DblClick(Cancel As Integer)
    DoCmd.OpenForm "formEditTabule", , , "TabDate = '" & Me.lstInventory & "'"
End Sub

This is the code that's giving me errors, the lstInvetory has got it's date from my db via SQL

SELECT Tabule1.TabKlient, Tabule1.TabOsoba, Tabule1.TabItem, Tabule1.TabQty, Tabule1.TabNote, Tabule1.TabAddedBy, Tabule1.TabDate, Tabule1.TabStatus
FROM Tabule1;

where the date column (TabDate) has it's format property set to General Date.
The text box on th go-to form which i want to open to a specific record, has it's format property set to General Date.

 

[bob fitz]

Try:
DoCmd.OpenForm "formEditTabule", , , "TabDate = #" & Me.lstInventory & "#"

 

[arnelgp]

the TabDate is on 7th column.

Private Sub lstInventory_DblClick(Cancel As Integer)
    DoCmd.OpenForm "formEditTabule", , , "TabDate = #" & Format(Me.lstInventory.Column(6),"mm/dd/yyyy") & "#"
End Sub

 

[mamradzelvy]

Try:
DoCmd.OpenForm "formEditTabule", , , "TabDate = #" & Me.lstInventory & "#"

This didn't work, it now thinks the date is on first row and outputs error 3075 "TabDate =#NameOfClient

 

[mamradzelvy]

the TabDate is on 7th column.

Private Sub lstInventory_DblClick(Cancel As Integer)
    DoCmd.OpenForm "formEditTabule", , , "TabDate = #" & Format(Me.lstInventory.Column(6),"mm/dd/yyyy") & "#"
End Sub

This didn't work either, also error 3075 "TabDate =#04.28.2020." which is not my format anyway as I'm European, so i swapped it around to "dd/mm/yyyy" which results in the same error, i tried adding "dd/mm/yyyy hh/nn/ss" but it still results in the same error, only now it displays #full date with time

 

[bob fitz]

What kind of data is in "TabDate". Just the date or time as well.

 

[mamradzelvy]

What kind of data is in "TabDate". Just the date or time as well.

Format : General Date
Default Value: Now()
Date type: Date and Time

(these are my guesses on the translation, my access client is in my native language)

 

[mamradzelvy]

What kind of data is in "TabDate". Just the date or time as well.

Example of what gets stored "28.04.2020 13:13:13"

 

[arnelgp]

is it true , you're date field has time value?

"DateValue(TabDate) = #" & Format(Me.lstInventory.Column(6),"mm/dd/yyyy") & "#"

 

[mamradzelvy]

is it true , you're date field has time value?

"DateValue(TabDate) = #" & Format(Me.lstInventory.Column(6),"mm/dd/yyyy") & "#"

yes it's true, this again results in a 3075: "DateValue(TabDate) = #04.28.2020."
even if i swap dd for mm

 

[mamradzelvy]

I just realized that when going with these commands you guys provided me, i get the output of time in 13.13.13 format, though everywhere else the time is saved as 13:13:13, might be the problem?

 

[Minty]

You are missing the closing # - remove all the ambiguity access will accept this format anywhere as far as I'm aware

"DateValue(TabDate) = #" & Format(Me.lstInventory.Column(6),"yyyy-mm-dd") & "#"

The DateValue at the beginning removes the time element for the comparison.

 

[arnelgp]

its a string... the value in Column 7?

"DateValue(TabDate) = #" & Format(CDate(Me.lstInventory.Column(6)),"mm/dd/yyyy") & "#"

 

[mamradzelvy]

You are missing the closing # - remove all the ambiguity access will accept this format anywhere as far as I'm aware

"DateValue(TabDate) = #" & Format(Me.lstInventory.Column(6),"yyyy-mm-dd") & "#"

The DateValue at the beginning removes the time element for the comparison.

althought i don't understand the slightest what diference you made, i suppose it's the dashes instead of the slashes, this doesn't result in an error, however, it only is able to select the first entry of the day, so i need the time value as well i think

 

[mamradzelvy]

its a string... the value in Column 7?

"DateValue(TabDate) = #" & Format(CDate(Me.lstInventory.Column(6)),"mm/dd/yyyy") & "#"

to be honest I'm not sure i know how to answer this, but to my knowledge it wouldn't be a string as it's a "Now()" property in the column, so i guess it's just date/time type data, sorry, I'm quite new to access or vba in general.

 

[onur_can]

Give the default value as a date, not as now, If you also want to keep the time information open the time field and assign it the default value as time.

 

[arnelgp]

it's definitely a string. you can try in immediate window:

m="28.04.2020 13:13:13"

using: Format(m ,"mm/dd/yyyy")
result: 28.04.2020 13:13:13

it does not add the two "/", therefore it is a string.

use the formula in post #13, and see what will happen.

 

[Minty]

Give the default value as a date, not as now, If you also want to keep the time information open the time field and assign it the default value as time.

This can be a really bad idea if you are trying to compare dates and times that might span more than one day. It's generally much better to have the entire datetime information in one field.

 

[mamradzelvy]

Guys i just scrapped the idea of opening via date altogether as it was too complicated for no good reason.
I have been trying now for well over an hour to make it open via the "ID" column and finally succeeded by going back to the original code i used to open links from listboxes and managed to make it work thanks to @Minty , however i had to reverse back from:

DoCmd.OpenForm "formEditTabule", , , "ID = '" & Me.lstInventory & "'"

To:

DoCmd.OpenForm "formEditTabule", , , "ID LIKE '" & Me.lstInventory.Column(8) & "'"

and in the end it was switching = for LIKE that did the trick, and i have absolutely no clue why that has worked and = didn't-could anybody please shed some light onto this for me?

it's confusing especially because i use an identical form and almost identical table for inventory and there the first code works flawlessly....

 

[Minty]

Is your ID a number field, if it is then try

DoCmd.OpenForm "formEditTabule", , , "ID = " & Me.lstInventory.Column(8)

This is because like will compare a number to a string but = won't, in fact I'm surprised you don't get a type mismatch error.

By the way, please rename the ID field to InventoryID or something more meaningful, later on, you will have 26 different ID fields in a query and no idea which one you are referring to.