problem with date comparison when querying in vba

**charis89** · 08-19-2015, 09:57 AM

I have a form with 2 textboxes named tbxFromDate & tbxToDate and a button.
User will enter from and to date in the format of "DD/MM/YYYY"
Table Table1 will contain data including ID,mm,dd,yyyy.
mm is month, dd is day and yyyy is year.
Table1 is a linked table so I cannot change the data
I am trying to retrieve data between the 2 dates user entered and create a new table.
The problem is, I think when I use DateValue, the date when converted from String, is not converted correctly due to the system date. Is there any way I can fix this without changing the date setting on the pc?

The codes are as shown below.

Code:

Private Sub btnTest_Click()

Dim dbs As DAO.Database

Dim strSQL As String
Const cstrNewTableName As String = "Table2"

Set dbs = CurrentDb

strSQL = "SELECT Table1.* INTO [" & cstrNewTableName & "] " & _
             "FROM Table1 " & _
             "WHERE (((DateValue(Format([mm],'00') & '/' & Format([dd],'00') & '/' & Format([yyyy],'0000'))) " & _
             "Between #" & Mid(Me.[tbxFromDate], 4, 3) & Left(Me.[tbxFromDate], 3) & Right(Me.[tbxFromDate], 4) & "# " & _
             "And #" & Mid(Me.[tbxToDate], 4, 3) & Left(Me.[tbxToDate], 3) & Right(Me.[tbxToDate], 4) & "#))" & _
             "ORDER BY Table1.ID;"

Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError

Exit_btnTest_Click:
    Exit Sub


Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
End Sub

So when I enter "01/04/2015" into tbxFromDate and "01/04/2015" into tbxToDate and click on the button, the query is ran and Table2 is created with data selected from Table1. But instead of getting data with date 1st April 2015, I am getting data with date 4th January 2015.

When I try and debug it, by adding Debug.Print, strSQL shows something like this:

Code:

SELECT Table1.* INTO [Table2] FROM Table1 WHERE (((DateValue(Format([mm],'00') & '/' & Format([dd],'00') & '/' & Format([yyyy],'0000'))) Between #04/01/2015# And #04/01/2015#) ORDER BY Table1.ID;

**ranman256** · 08-19-2015, 10:08 AM

dont worry about datevalue,
just concatinate the date items [mm] & '/' & [dd] & '/' & [yyyy]
then append them into the date field. Access knows its a date, no formatting needed.

(dont do a MAKE table, create your table in advance (or edit a make table result), with 'date' data type, then run an APPEND query)

**charis89** · 08-19-2015, 10:28 AM

I created Table2 with the same fields as Table1 and tried to append instead, it is still getting data with date 4th January 2015, instead of 1st April 2015.

**June7** · 08-19-2015, 11:19 AM

Review http://allenbrowne.com/ser-36.html

**ranman256** · 08-19-2015, 12:02 PM

then the data is wrong. unless you have month and day swapped.
the query is sound.

**charis89** · 08-19-2015, 12:41 PM

Originally Posted by June7

Review http://allenbrowne.com/ser-36.html

I've read that before. After reading, I added the '#' for the dates taken from the texboxes. Issue now is, I think maybe the formatting of the dates taken from the database since I am unable to add '#' to those.

**ssanfu** · 08-19-2015, 02:25 PM

Your WHERE clause is wrong.

"WHERE (((DateValue(Format([mm],'00') & '/' & Format([dd],'00') & '/' & Format([yyyy],'0000'))) " & _
"Between #" & Mid(Me.[tbxFromDate], 4, 3) & Left(Me.[tbxFromDate], 3) & Right(Me.[tbxFromDate], 4) & "# " & _
"And #" & Mid(Me.[tbxToDate], 4, 3) & Left(Me.[tbxToDate], 3) & Right(Me.[tbxToDate], 4) & "#))" & _

First, the part in RED would/must be concantated, not inside the quotes.
More to the point, you are missing the FIELD name for the criteria.
Looking at the result of the Debug.Print statement

Code:

WHERE (((DateValue(Format([mm],'00') & '/' & Format([dd],'00') & '/' & Format([yyyy],'0000'))) Between #04/01/2015# And #04/01/2015#)

what FIELD should the criteria be applied to???

Create a new SELECT query.
Add the fields from "Table1"
Now manually add the criteria to the date field: BETWEEN #04/01/2015# and #04/01/2015# <<= (April 1, 2015)
Execute the query. Is the data correct?
If yes. switch to SQL view. What does the WHERE clause look like?

I would recommend creating "Table2" one time and use an Append query rather than a make table query.

Your code would/should look something like:

Code:

Option Compare Database   '<- should be at the top of EVERY module
Option Explicit           '<- should be at the top of EVERY module

Private Sub btnTest_Click()
    On Error GoTo Exit_btnTest_Click

    Const cstrNewTableName As String = "Table2"

    Dim dbs As DAO.Database
    Dim strSQL As String

    Set dbs = CurrentDb

    '    strSQL = "SELECT Table1.* INTO [" & cstrNewTableName & "] "
    strSQL = "SELECT Table1.*"
    strSQL = strSQL & " FROM Table1"
    strSQL = strSQL & " WHERE Table1.MyDateField"
    strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " ORDER BY Table1.ID;"

    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError

Exit_btnTest_Click:
    Set dbs = Nothing
    Exit Sub

Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
End Sub

Change MyDateField to your field name.

**charis89** · 08-19-2015, 07:46 PM

Originally Posted by ranman256

then the data is wrong. unless you have month and day swapped.
the query is sound.

I don't think the data is wrong. I created Table1 using the only 4 fields with the same details as the linked table and inserted a few rows of sample data.

Click image for larger version.

Name: Table1_Data.JPG
Views: 9
Size: 26.4 KB
ID: 21745

Table1 Data basically looks like this.

Click image for larger version.

Name: Form1_w_Date.JPG
Views: 9
Size: 16.7 KB
ID: 21743

My form looks like this

Click image for larger version.

Name: Table1_Details.JPG
Views: 9
Size: 49.9 KB
ID: 21744

Table1 Design View

Click image for larger version.

Name: Table2_Details.JPG
Views: 9
Size: 54.8 KB
ID: 21746

Table2 Design View

I tried this at first to ignore the textboxes and hardcode dates in to test but it did not work. No data was inserted into Table2.
Ignoring the face that I forgot to insert data into "yyyy" field.

Code:

Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click


    Dim dbs As DAO.Database
    
    Dim strSQL As String
'    Const cstrNewTableName As String = "Table2"
    
    Set dbs = CurrentDb
    
    strSQL = "INSERT INTO Table2 ( ID, mm, dd, fullDate ) " & _
             "SELECT Table1.ID, Table1.mm, Table1.dd, [mm] & '/' & [dd] & '/' & [yyyy] AS date1 " & _
             "FROM Table1 " & _
             "WHERE ((([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));"
    
    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError


Exit_btnTest_Click:
    Set dbs = Nothing
    Exit Sub


Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
    
End Sub

So I tried this instead

Code:

Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click


    Dim dbs As DAO.Database
    
    Dim strSQL As String
'    Const cstrNewTableName As String = "Table2"
    
    Set dbs = CurrentDb
    
    strSQL = "INSERT INTO Table2 ( ID, mm, dd, fullDate ) " & _
             "SELECT Table1.ID, Table1.mm, Table1.dd, [mm] & '/' & [dd] & '/' & [yyyy] AS date1 " & _
             "FROM Table1 " & _
             "WHERE ((DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));"
    
    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError


Exit_btnTest_Click:
    Set dbs = Nothing
    Exit Sub


Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
    
End Sub

Debug.Print shows

Code:

INSERT INTO Table2 ( ID, mm, dd, fullDate ) SELECT Table1.ID, Table1.mm, Table1.dd, [mm] & '/' & [dd] & '/' & [yyyy] AS date1 FROM Table1 WHERE ((DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));

And Table2 was populated with 1 row.

Click image for larger version.

Name: Table2_Data2.JPG
Views: 9
Size: 24.4 KB
ID: 21747

Once again, ignoring the face that I forgot to insert data into "yyyy" field.
Although date shown in "fullDate" is correct, the data in "dd", "mm" is wrong.
It should be "1" in "dd", "4" in "mm".
And from the data in Table1, it should be 2 rows, "ID" 1 & 4 instead of "ID" 3 which was inserted into Table2

Originally Posted by ssanfu

Your WHERE clause is wrong.

First, the part in RED would/must be concantated, not inside the quotes.
More to the point, you are missing the FIELD name for the criteria.
Looking at the result of the Debug.Print statement

Code:

WHERE (((DateValue(Format([mm],'00') & '/' & Format([dd],'00') & '/' & Format([yyyy],'0000'))) Between #04/01/2015# And #04/01/2015#)

what FIELD should the criteria be applied to???

Create a new SELECT query.
Add the fields from "Table1"
Now manually add the criteria to the date field: BETWEEN #04/01/2015# and #04/01/2015# <<= (April 1, 2015)
Execute the query. Is the data correct?
If yes. switch to SQL view. What does the WHERE clause look like?

I would recommend creating "Table2" one time and use an Append query rather than a make table query.

Your code would/should look something like:

Code:

Option Compare Database   '<- should be at the top of EVERY module
Option Explicit           '<- should be at the top of EVERY module

Private Sub btnTest_Click()
    On Error GoTo Exit_btnTest_Click

    Const cstrNewTableName As String = "Table2"

    Dim dbs As DAO.Database
    Dim strSQL As String

    Set dbs = CurrentDb

    '    strSQL = "SELECT Table1.* INTO [" & cstrNewTableName & "] "
   strSQL = "SELECT Table1.*"
    strSQL = strSQL & " FROM Table1"
    strSQL = strSQL & " WHERE Table1.MyDateField"
    strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " ORDER BY Table1.ID;"

    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError

Exit_btnTest_Click:
    Set dbs = Nothing
    Exit Sub

Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
End Sub

Change MyDateField to your field name.

Thing is, I don't have 1 field that is a date field. It is 3 separate fields("mm","dd","yyyy").
I tried creating Table2 with the same fields plus 1 called fullDate then changing my sql to something like you showed but it still doesn't work.

Code:

Option Compare Database
Option Explicit


Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click


    Dim dbs As DAO.Database
    
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
    strSQL = "INSERT INTO Table2 ( ID, mm, dd )"
    strSQL = strSQL & " SELECT Table1.ID, Table1.mm, Table1.dd"
    strSQL = strSQL & " FROM Table1"
    strSQL = strSQL & " WHERE ([mm] & '/' & [dd] & '/' & [yyyy])"
    strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " ORDER BY Table1.ID;"
    
    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError


Exit_btnTest_Click:
    Set dbs = Nothing
    Exit Sub


Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
    
End Sub

No data was inserted into Table2

Debug.Print shows

Code:

INSERT INTO Table2 ( ID, mm, dd ) SELECT Table1.ID, Table1.mm, Table1.dd FROM Table1 WHERE ([mm] & '/' & [dd] & '/' & [yyyy]) Between #04/01/2015# And #04/01/2015# ORDER BY Table1.ID;

When I changed it to this, same thing happened as above, where 1 row is inserted but the wrong data is inserted.

Code:

Option Compare Database
Option Explicit


Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click


    Dim dbs As DAO.Database
    
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
    strSQL = "INSERT INTO Table2 ( ID, mm, dd )"
    strSQL = strSQL & " SELECT Table1.ID, Table1.mm, Table1.dd"
    strSQL = strSQL & " FROM Table1"
    strSQL = strSQL & " WHERE DateValue([mm] & '/' & [dd] & '/' & [yyyy])"
    strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
    strSQL = strSQL & " ORDER BY Table1.ID;"
    
    Debug.Print strSQL
    dbs.Execute strSQL, dbFailOnError


Exit_btnTest_Click:
    Set dbs = Nothing
    Exit Sub


Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
    
End Sub

Debig.Print shows

Code:

INSERT INTO Table2 ( ID, mm, dd ) SELECT Table1.ID, Table1.mm, Table1.dd FROM Table1 WHERE DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #04/01/2015# And #04/01/2015# ORDER BY Table1.ID;

**ssanfu** · 08-20-2015, 04:48 PM

Once again: The part in RED must be concatenated.

Inside the quotes, it becomes a literal string! No different than "Hi There". The DateValue() function is not executed!
Look at your debug statements. Do you see dates or a string with DateValue in it?

This is a string:

Code:

"WHERE ((DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));"

This will concatenate a date:

Code:

"WHERE ((" & DateValue([mm] & '/' & [dd] & '/' & [yyyy]) & " Between #4/1/2015# And #4/1/2015#));"

Maybe the attached dB will help...

problem with date comparison when querying in vba

Thread Tools

problem with date comparison when querying in vba

Similar Threads

Date comparison return certain information.

RC Notation and Date Comparison

VBA problem with IF comparison. HELP.

Date Comparison swapping month and day in VB

Short date comparison

Tags for this Thread

Posting Permissions