Trouble formatting text and filtering records with the result

N

Night Owl

Hi,

For some reason known only to a select few, a dump file in a database
provides date and time information when a specific incident takes place, in
the following format...

2004-09-01-01.14.00.000000

This relates (obviously) to the date, time and some rubbish that's of no use
to me. I do, however, want to use the date (in this case 2004-09-01) and
the time (01.14.00).

No matter what I do, and irrespective of how I format the date (I haven't
got to the time yet!) I cannot search for records within a specific date
range. I've got a query that uses --- Date: Format(Left([DateTime],10),"dd
mmmm yyyy") --- to provide the date, and I can use an On_Click event for a
command button to open fields using --- [Date] = 01 September 2004 ---, but
when I use --- [Date] Between 01 September 2004 And 01 October 2004 --- all
records are shown. Worryingly, the results from this query return records
where the date is the 10th of every month, i.e. 10 September 2004, 10
October 2004, 10 November 2004, etc. etc.

Where am I going wrong formatting the date? Any guidance would be
appreciated.

TIA,

Pete
 
M

MGFoster

Night said:
Hi,

For some reason known only to a select few, a dump file in a database
provides date and time information when a specific incident takes place, in
the following format...

2004-09-01-01.14.00.000000

This relates (obviously) to the date, time and some rubbish that's of no use
to me. I do, however, want to use the date (in this case 2004-09-01) and
the time (01.14.00).

No matter what I do, and irrespective of how I format the date (I haven't
got to the time yet!) I cannot search for records within a specific date
range. I've got a query that uses --- Date: Format(Left([DateTime],10),"dd
mmmm yyyy") --- to provide the date, and I can use an On_Click event for a
command button to open fields using --- [Date] = 01 September 2004 ---, but
when I use --- [Date] Between 01 September 2004 And 01 October 2004 --- all
records are shown. Worryingly, the results from this query return records
where the date is the 10th of every month, i.e. 10 September 2004, 10
October 2004, 10 November 2004, etc. etc.

Where am I going wrong formatting the date? Any guidance would be
appreciated.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe I'd use Data data type values in the BETWEEN expression.
Also, don't use "Date" as a column name, it is a reserved key word in
VB, and it is not very descriptive - what date? Sales Date? Date of
birth? Death date?. E.g.:

IncidentDate: CDate(Format(Left([DateTime],10),"General Date"))

Use delimited dates (USA date format: m/d/yy) for the BETWEEN
expression:

IncidentDate BETWEEN #9/1/2004# And #10/1/2004#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpNxq4echKqOuFEgEQIp7ACff488ABppa0ams2jTHV1tYJZk6WcAn011
c3apZZXPVD3Hb1YfZ8+enPBW
=JGl3
-----END PGP SIGNATURE-----
 
N

Night Owl

And I believe you're absolutely right - thank you.

Pete

MGFoster said:
Night said:
Hi,

For some reason known only to a select few, a dump file in a database
provides date and time information when a specific incident takes place,
in the following format...

2004-09-01-01.14.00.000000

This relates (obviously) to the date, time and some rubbish that's of no
use to me. I do, however, want to use the date (in this case 2004-09-01)
and the time (01.14.00).

No matter what I do, and irrespective of how I format the date (I haven't
got to the time yet!) I cannot search for records within a specific date
range. I've got a query that uses --- Date:
Format(Left([DateTime],10),"dd mmmm yyyy") --- to provide the date, and I
can use an On_Click event for a command button to open fields using ---
[Date] = 01 September 2004 ---, but when I use --- [Date] Between 01
September 2004 And 01 October 2004 --- all records are shown.
Worryingly, the results from this query return records where the date is
the 10th of every month, i.e. 10 September 2004, 10 October 2004, 10
November 2004, etc. etc.

Where am I going wrong formatting the date? Any guidance would be
appreciated.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe I'd use Data data type values in the BETWEEN expression.
Also, don't use "Date" as a column name, it is a reserved key word in
VB, and it is not very descriptive - what date? Sales Date? Date of
birth? Death date?. E.g.:

IncidentDate: CDate(Format(Left([DateTime],10),"General Date"))

Use delimited dates (USA date format: m/d/yy) for the BETWEEN
expression:

IncidentDate BETWEEN #9/1/2004# And #10/1/2004#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpNxq4echKqOuFEgEQIp7ACff488ABppa0ams2jTHV1tYJZk6WcAn011
c3apZZXPVD3Hb1YfZ8+enPBW
=JGl3
-----END PGP SIGNATURE-----
Click to expand...
 
Top