Search form/query with dates - getting mismatch errors

[neobrainless]

Hello,
I'm not great at VBA but have hammered someone else's code found on the internet into shape except for a couple of issues, the one I'm currently working on is that I can't search for a date. Someone on here mentioned use of #'s but I'm not sure how with the code I currently have - could be I need to re-write the section for the date..?

Currently I have:

Private Sub btnSearch_Click()
    
    Dim sqlinput As Variant
    
    ' Update the record source
    sqlinput = "SELECT * FROM FileInfQry " & BuildFilter
    
    Debug.Print sqlinput
    
    Me.FileInfSubForm.Form.RecordSource = sqlinput
    
    ' Requery the subform
    Me.FileInfSubForm.Requery

which refers too:

    ' Check for Date Created
    If Me.txtDateCreated > "" Then
        varWhere = varWhere & "[DateCreated] = '" & Me.txtDateCreated & "' AND "
    End If

Any help is very gratefully received! Thanks :)

[zmbd]

Yes, anytime you have a "Date" field that you are pulling data from you should normally enclose the criteria within the "#" signs

So in the small block of code you posted, if the field [DateCreated] is formated at the table level as a date field, then you would replace the single quotes ( " ' ") with the "#" to get something like:

    ' Check for Date Created 
    If Me.txtDateCreated > "" Then 
        varWhere = varWhere & "[DateCreated] = #" & Me.txtDateCreated & "# AND " 
    End If

Mind you, I have not checked your code for any errors etc... I've only showed you where to place the "#"

[neobrainless]

That's great, thanks, cleared the initial issue. Now that's highlighted something different - when I search for '08/04/1991' or '07/05/1991' I get nothing (despite there being a record with that date), but when I search '13/08/1991' or '20/08/1991' I get hits... Is it something to do with the preceding '0'? Or is my code just that broken? :P

Thanks for the help :)

[zmbd]

It shouldn't matter so long as the date is within the "#" If you use a single or double quote you will have issues searching on a date-field. If the field happens to be text, then the search is quite litteral and the entry "00" does not equal "0" etc...

Please go back in to your table in design view and double check what data type has been set for the field.

[neobrainless]

Hmm, I've checked and the original table is set to Date/Time for that field.

To clarify - I'm not using the quotes as part of the search terms, just typing in: 07/05/1991

Cheers

[zmbd]

Line 8 of your first code block you have a debug print.
Please post the results of that line, please click on the [CODE/] button first and place the result between the two code tads.
Also, which version of access are you using/

[neobrainless]

Hi, print out is:

SELECT * FROM FileInfQry WHERE [DateCreated] = #08/04/1991#

I'm using Access 2007.

Cheers :)

[MikeTheBike]

Hi

The # delimiter for dates is a common problem with dates. I would suggest the most common problem if you are un the UK (?), or a region setting that doesn't use US dates, is the date format when using VB/VBA.

Based on you dates given in you posts this would seem to be the case. ie. dates '13/08/1991' or '20/08/1991' cannot be anything else but August as months 13 and 20 do not exist, hence tyhese work OK. However, '08/04/1991' or '07/05/1991' would be interpreted as 4th August and 5th July.

To overcome this when using variable you need to format the date ie.

...#" & Format(DateVariable,"MM/DD/YYYY") & "# ..

HTH

I believe there are articles in this forum explaining this better than this, but not sure where !!??


MTB

[neobrainless]

Thanks for the hint, soundsl ike you could be right. However, having tried to change the code, I am now gettign an error:

Run time error '2455'
You entered an expression that has an invalid reference to the property RecordSource

I've got no idea what's changed - the line I changed now looks like:

        varWhere = varWhere & "[DateCreated] #" & Format(Me.txtDateCreated, "DD/MM/YYYY") & "# AND "

So unless I've made a stupid mistake in there I'm lost? :/

Thanks for your help :)

[zmbd]

MTB,
That was my next statement about the dates... Just wanted to make sure that the string was resolving correctly.
Note the string posted in #7 isn't what I'm expecting from the code in the second block in #1

Allen Browne's site -International Dates in Access
(....)
2. Wrong Formatting in Code
In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

To demonstrate this, enter any date in the Criteria row under a date field in Query Design, and then switch to SQL View. In Query Design view, you see the date according to your local settings, but the SQL statement uses mm/dd/yyyy format.

SQL clauses are not always obvious, e.g. the Filter of a form, the WhereCondition of OpenReport, or the third argument of a domain aggregate function. Examples:

•DoCmd.OpenRepo rt "MyReport", acViewPreview, , "InvoiceDat e > #12/31/2000#"
•Debug.Pri nt DLookup("Studen tID", "tblStudent ", "EntryDate = #6/30/1953#")
•strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.Start Date, "mm\/dd\/yyyy") & "#;" (....)

last part of your code is (...) ") & "# AND "
What follows?
Please post the entire code block.

[zmbd]

Just noted:Format(Me.txtDa teCreated, "DD/MM/YYYY")
should be: Format(Me.txtDa teCreated, "MM/DD/YYYY")

One thing to note:
{edit - reworded the following...}
If your PC has the local settings to English(USA) then if you have April 12, 2013 stored in the European format "12/04/2013" then using Format(Me.txtDa teCreated, "MM/DD/YYYY") will often return 12/04/2013 not 04/12/2013 as expected. The reason is because format recognizes the 12/04/2013 to be a valid date.

[neobrainless]

Hi,
I believe the 'AND' at the end of the line is surplus to requirements (possibly somethign to do with the wildcards in the other lines I just copied over?).

Anyway, the whole block is:

    ' Check for Date Created
    If Me.txtDateCreated > "" Then
        varWhere = varWhere & "[DateCreated] #" & Format(Me.txtDateCreated, "MM/DD/YYYY") & "# AND "
    End If

Cheers :)

[zmbd]

Try fixing the format command first.

Otherwise, we'll need to start a new thread for the 2455 error - if we do need to, please post all of the code (from sub *() thru end sub)

[neobrainless]

Ok, I'm a little confused now - My PC is in England, set to DD/MM/YYYY format. Which format do I need to force in VBA?! Tbh, I've tried both with no change in result, so I guess it's likely not that that's the problem?

[zmbd]

Did you read AB site?

Your date, as set in code, should be in the "mm/dd/yyyy" format.

If your region/local setting (via the OS Control Panel) is set to have the date as "DD/MM/YYYY" then when you use the format function as given, it should convert the date the the "mm/dd/yyyy" format expected by the Access engine.

If you are still getting the 2455 error despite fixing the date format, then there is an issue deeper within your code.

Start with: > Before Posting (VBA or SQL) Code
You may need to repeat the compile step several times as the compiler with stop on the first error it finds.
You will need to start a new thread for the 2455 troubleshooting .