comparing dates

[Junaid Hussain]

Hi,

I have a MS Access database(Calls.mdb) which contains data for 'Calls
received' to a particular helpdesk . It contains the following columns;

1) Call ID [Number]
2) Status [Text]
3) date_Created [ DD/MM/YYYY HH:MM:SS]

I now want to create some reports in MS Excel using the data from the
database (Calls.mdb). I have created a macro in Excel which connects to the
database and runs the following code along with an SQL query.

//-----------------------------------
dim StartDate As Date
dim CloseDate As Date

StartDate = "01/05/2005 00:00:00"
CloseDate = "01/05/2005 23:59:59"

querySQL = "SELECT * FROM calls_logged WHERE date_Created between #" &
StartDate & "# AND #" & CloseDate & "# "
//-----------------------------------

The Access database contains records dating from 20/04/05 to 09/04/05. When
I run the the macro (the above code is a simplified extract) it returns all
the records from the database even though my aim with the above query is to
get only the records whose date lies on 01/05/2005. I cannot see any
problems with my code. Please can anyone help.

Regards,
Junaid Hussain
(Student)

 

[Ken Snell [MVP]]

You need to use the mm/dd/yyyy format for your query, as Jet (the database
engine) expects date strings to be in that format. So either change how you
set up your strings:
StartDate = "05/01/2005 00:00:00"
CloseDate = "05/01/2005 23:59:59"

or use CDate and Format to restructure the strings you're currently using:

querySQL = "SELECT * FROM calls_logged WHERE date_Created between #" &
Format(CDate(StartDate), "mm/dd/yyyy") & "# AND #" &
Format(CDate(CloseDate), "mm/dd/yyyy") & "# "

Also, you note that "The Access database contains records dating from
20/04/05 to 09/04/05." Assuming that these are both in the dd/mm/yyyy
format, it would appear that your database table will not contain any
records with a May 1 date anyway?

 

[Ken Snell [MVP]]

I also see another problem here. You're declaring StartDate and CloseDate as
Date variables, and then setting them equal to strings. This will not work.

If you want to use them as Date variables, then use this:

//-----------------------------------
dim StartDate As Date
dim CloseDate As Date

StartDate = CDate("01/05/2005 00:00:00")
CloseDate = CDate("01/05/2005 23:59:59")

querySQL = "SELECT * FROM calls_logged WHERE date_Created between " &
StartDate & " AND " & CloseDate
//-----------------------------------

What you were trying to do would be the appropriate direction if you'd
declared StartDate and CloseDate as String variables.

 

[Junaid Hussain]

Sorry I meant that I have data from 20/04/2005 to 09/05/2005.
Anyway thank you very much for your help. I am going to give it a try with
both your solutions.

Regards,
Junaid Hussain

You need to use the mm/dd/yyyy format for your query, as Jet (the database
engine) expects date strings to be in that format. So either change how
you set up your strings:
StartDate = "05/01/2005 00:00:00"
CloseDate = "05/01/2005 23:59:59"

or use CDate and Format to restructure the strings you're currently using:

querySQL = "SELECT * FROM calls_logged WHERE date_Created between #" &
Format(CDate(StartDate), "mm/dd/yyyy") & "# AND #" &
Format(CDate(CloseDate), "mm/dd/yyyy") & "# "

Also, you note that "The Access database contains records dating from
20/04/05 to 09/04/05." Assuming that these are both in the dd/mm/yyyy
format, it would appear that your database table will not contain any
records with a May 1 date anyway?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have a MS Access database(Calls.mdb) which contains data for 'Calls
received' to a particular helpdesk . It contains the following columns;

1) Call ID [Number]
2) Status [Text]
3) date_Created [ DD/MM/YYYY HH:MM:SS]

I now want to create some reports in MS Excel using the data from the
database (Calls.mdb). I have created a macro in Excel which connects to
the database and runs the following code along with an SQL query.

//-----------------------------------
dim StartDate As Date
dim CloseDate As Date

StartDate = "01/05/2005 00:00:00"
CloseDate = "01/05/2005 23:59:59"

querySQL = "SELECT * FROM calls_logged WHERE date_Created between #" &
StartDate & "# AND #" & CloseDate & "# "
//-----------------------------------

The Access database contains records dating from 20/04/05 to 09/04/05.
When I run the the macro (the above code is a simplified extract) it
returns all the records from the database even though my aim with the
above query is to get only the records whose date lies on 01/05/2005. I
cannot see any problems with my code. Please can anyone help.

Regards,
Junaid Hussain
(Student)

 

[Junaid Hussain]

Excellent,

I changed two lines of code into

StartDate = CDate("mm/dd/yyyy hh:mm")
CloseDate = CDate("mm/dd/yyyy hh:mm")

and it now works smooth. Thanks for your help.

Regards
Junaid

I also see another problem here. You're declaring StartDate and CloseDate
as Date variables, and then setting them equal to strings. This will not
work.

If you want to use them as Date variables, then use this:

//-----------------------------------
dim StartDate As Date
dim CloseDate As Date

StartDate = CDate("01/05/2005 00:00:00")
CloseDate = CDate("01/05/2005 23:59:59")

querySQL = "SELECT * FROM calls_logged WHERE date_Created between " &
StartDate & " AND " & CloseDate
//-----------------------------------

What you were trying to do would be the appropriate direction if you'd
declared StartDate and CloseDate as String variables.

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have a MS Access database(Calls.mdb) which contains data for 'Calls
received' to a particular helpdesk . It contains the following columns;

1) Call ID [Number]
2) Status [Text]
3) date_Created [ DD/MM/YYYY HH:MM:SS]

I now want to create some reports in MS Excel using the data from the
database (Calls.mdb). I have created a macro in Excel which connects to
the database and runs the following code along with an SQL query.

//-----------------------------------
dim StartDate As Date
dim CloseDate As Date

StartDate = "01/05/2005 00:00:00"
CloseDate = "01/05/2005 23:59:59"

querySQL = "SELECT * FROM calls_logged WHERE date_Created between #" &
StartDate & "# AND #" & CloseDate & "# "
//-----------------------------------

The Access database contains records dating from 20/04/05 to 09/04/05.
When I run the the macro (the above code is a simplified extract) it
returns all the records from the database even though my aim with the
above query is to get only the records whose date lies on 01/05/2005. I
cannot see any problems with my code. Please can anyone help.

Regards,
Junaid Hussain
(Student)