Datetype stLinkCriteria problem

[botci]

Hello,
I want to open a subform with this code:

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "validalas"
       
    If IsNull([Nyilvantartasi szam]) And Not IsNull([Cim]) Then
               stLinkCriteria = "[Cim]=" & "'" & Me![Cim] & "'"
    End If
    
    If IsNull([Cim]) And Not IsNull([Nyilvantartasi szam]) Then
       stLinkCriteria = "[Nyilvantartasi szam]=" & "'" & Me![Nyilvantartasi szam] & "'"    
    End If
    
    If Not IsNull([Cim]) And Not IsNull([Nyilvantartasi szam]) Then
          stLinkCriteria = "[Nyilvantartasi szam]=" & "'" & Me![Nyilvantartasi szam] & "'"
    End If
    
    If IsNull([Cim]) And IsNull([Nyilvantartasi szam]) Then
          stLinkCriteria = "[Datum]='" & Me![Datum] & " '"
    End If
       
    DoCmd.OpenForm stDocName, , , stLinkCriteria

[Nyilvantartasi szam] and [Cim] are text type, [Datum] is date type, so in the 4th case when
stLinkCriteria = "[Datum]='" & Me![Datum] & " '"
I get the error: "Data type mismatch in criteria expression"
What is the right syntax for date type stLinkCriteria ?
Thanks for your help.

 

[Minty]

Dates have to be in the format #mm/dd/yyyy# - note the #'s that are used by access to delimit the date.

 

[botci]

Thank you Minty, I tried it, but what is the exact syntax?

with this:

stLinkCriteria = "[Dátum]=#" & Me![Dátum] & " #"

I get syntax error

 

[Minty]

In your example it would be

stLinkCriteria = "[Datum]=#" & Format(Me![Datum],"mm/dd/yyyy") & "#"

I would advice changing your Field name from Datum, as I suspect its possibly a reserved word in your locale? That is why you have to put it in square brackets.

 

[botci]

I changed a little your code, because my regional date format is yyyy.mm.dd.

stLinkCriteria = "[Datum]=#" & Format(Me![Datum], "yyyy.mm.dd.") & "#"

But I get the error:
Syntax error in date in query expression
'[Datum]=#2017.03.13.'.

 

[Minty]

I don't think that will work - no matter what your local format is Access uses the format of #mm/dd/yyyy#

I'm not convinced about the trailing . even if it would accept your format.

stLinkCriteria = "[Datum]=#" & Format(Me![Datum], "yyyy.mm.dd") & "#"

 

[botci]

OK, I tried your original code:

stLinkCriteria = "[Datum]=#" & Format(Me![Datum],"mm/dd/yyyy") & "#"

still the error :
Syntax error in date in query expression
'[Datum]=#03.13.2017'.

 

[Minty]

This doesn't make sense

Syntax error in date in query expression
'[Datum]=#03.13.2017'.

You don't appear to be setting this correctly - where is the second # , and where are the / ?

Try adding a debug.print stLinkCriteria after you have set the criteria , and look in the immediate window (Ctrl + G) in the vb editor.

 

[botci]

this is the debug:
[Datum]=#03.13.2017#
the second # is OK, no /

 

[Minty]

And you are still getting the type mismatch? Is Datum actually really a date field?

What happens if you hard code a value in there [Datum]=#03/13/2017# ?

 

[botci]

yes, still getting the type mismatch, and sure it is a date field.

hard coding the value : [Datum]=#03/13/2017#
and stLinkCriteria = [Datum]

and I get:
Syntax error in number in query expression : '2017.03.13'.

and for debug.print stLinkCriteria I get:
2017.03.13.

 

[botci]

Now, I figured out, there is somehing wrong with my local date format.
If I set windows to to
mm/dd/yyyy
format, everything OK.

But setting it to my custom local format: yyyy.mm.dd.
I can't make it work.
If nobody can't solve this discrepancy, I have to bow to using the here inconvenient date format.
Thanks your help, anyway

 

[Minty]

I think that the problem might be that your local format is a custom one and not inbuilt. I also suspect the trailing . on the end of your custom format might be an issue.

Thanks for coming back to the thread though.
Out of interest what is your region? Just if others ever have the same problem.

 

[botci]

I used custom in the sense that it is common.
This format is built in the hungarian windows
I set it custom to mm/dd/yyyy format, it is not built in this windows.
I tried yyyy.mm.dd. (which is default), and without dot at the end yyyy.mm.dd
but no success.
Thanks for your help

 

[botci]

Success!

I searched the net and found the right syntax for any locale here:
http://allenbrowne.com/ser-36.html

So in my case it works with this:

stLinkCriteria = "[Datum]=" & Format(Me![Datum], "\#mm\/dd\/yyyy\#")

 

[Minty]

Excellent - I use the SQLDate() function on that page all the time as being in the UK we can get caught out by the dd/mm/yyyy mis-formatting.

I had forgotten that it access forces the Format() function to use the local settings unless you specifically used a delimiter.