Find records between two dates in Access 2002 using VB6

  • Thread starter Thread starter rxkhan via AccessMonster.com
  • Start date Start date
R

rxkhan via AccessMonster.com

I have an sql query that finds dates between two specified dates. Query
works fine with start date that is it starts printing or retrieving records
from the specified start date but it does not recognises the enddate that is
it proceed further ahead the enddate.

em using vb6 and MS Access 2002

here is my query

Source = "Select * from [FEA_List_From_Carpenters] where [Transaction Date]
Between #" & DTPickerFrom& "# And #" & DTPickerTo & "# ORDER BY [Transaction
Date]"

Transaction Date is of DATE/TIME Data type.

Please help!!!
 
It looks fine if your default "Short Date" format on the OS is "mm/dd/yyyy"
since explicit date values in JET must be of the format "mm/dd/yyyy". If
your default "Short Date" format is different from the above, you need to
use Format() function to change the date String to the above format in your
SQL String construction.
 
Van said:
explicit date values in JET must be of the format "mm/dd/yyyy".
Click to expand...

Jet also recognises the international standard (unambiguous) date
format of yyyy-mm-dd.
 
True but the common error is that people where their default date format is
"dd/mm/yyyy" expect JET to recognise this with # delimiters and interpret
the correct date value which I am trying to address.

Also, if you use the international date format in the SQLView of the Access
Query interface then switch to DesignView and save the Query. When you open
the Query again in SQLView, Access will switch the explicit date value to
#mm/dd/yyyy#. Hence it is common for us to use the format #mm/dd/yyyy# in
Access (or JET in Access).
 
Van said:
if you use the international date format in the SQLView of the Access
Query interface then switch to DesignView and save the Query. When you open
the Query again in SQLView, Access will switch the explicit date value to
#mm/dd/yyyy#.
Click to expand...

Grrr! Don't you hate it when a machine decides to change one's code?!

I revise my suggestion to the (almost) international, unambiguous and
Access-proof #yyy-mmm-dd# :)
 
it worked....thanks...but problem now is the importing txt files in access
with date as shortdate does not imports records where date is more then 12th
of each month....if cnahge the date type to text it works as well as the
query...
 
I am not sure how this is related to the original question but clearly in
your importing process, JET expects the data input in the format
"mm/dd/yyyy" but you provided the date input in the format "dd/mm/yyyy".
Hence, JET will reject value such as "20/07/2005" since the month value is
invalid according to JET's expectation.

How do you import the Text files?

--
HTH
Van T. Dinh
MVP (Access)
 
using the normnal import process: File > Get External Date > etc...is there a
way the system can automatically do the importing if we provide it with the
file location?
I am not sure how this is related to the original question but clearly in
your importing process, JET expects the data input in the format
"mm/dd/yyyy" but you provided the date input in the format "dd/mm/yyyy".
Hence, JET will reject value such as "20/07/2005" since the month value is
invalid according to JET's expectation.

How do you import the Text files?
it worked....thanks...but problem now is the importing txt files in access
with date as shortdate does not imports records where date is more then
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
using the normnal import process: File > Get External Date > etc...is there a
way the system can automatically do the importing if we provide it with the
file location?
Click to expand...

You can query the data e.g.

SELECT key_col, data_col
FROM [Text;HDR=Yes;Database=C:\MyFolder;].[MyFile#txt]

and use a schema.ini file to specifiy the data type e.g. you could
specify to import as text then in the SQL parse for date format and
cast as DATETIME.
 
What is the Date Format set in the Regional Settings of your OS?

Is this the same with the format in the Text file you are trying to import?
 
Back
Top