querying on date ... to/from

[Guest]

I have a date field in a 3rd party database where dates are stored as year,
month, day (i.e. May 6, 2005 is 20050506).

I have to enter the leading zero on single digit months or days to query (no
biggie) BUT ... I can filter from this date forward (>=[Starting Date]), but
not from one date to another (>=[Starting Date] And <=[Ending Date]).

What am I doing wrong? Surely it's possible to query to/from a date in this
format, isn't it?

 

[Jeff Boyce]

Jackie

It's done all the time, both as you've tried (>=, ... <=), and using the
expression "Between ... And ...".

If your attempt isn't working, what isn't working? Are you getting an error
message? Are you getting incorrect results?

More info, please...
Jeff Boyce
<Access MVP>

 

[Guest]

When I query only with a starting date, I get everything from that date
forward. When I query to/from, I get no error and a blank datasheet. I've
never used between/and. I'll try it and see if that makes any difference.

Jackie

It's done all the time, both as you've tried (>=, ... <=), and using the
expression "Between ... And ...".

If your attempt isn't working, what isn't working? Are you getting an error
message? Are you getting incorrect results?

More info, please...
Jeff Boyce
<Access MVP>

I have a date field in a 3rd party database where dates are stored as year,
month, day (i.e. May 6, 2005 is 20050506).

I have to enter the leading zero on single digit months or days to query
(no
biggie) BUT ... I can filter from this date forward (>=[Starting Date]),
but
not from one date to another (>=[Starting Date] And <=[Ending Date]).

What am I doing wrong? Surely it's possible to query to/from a date in
this
format, isn't it?

 

[Guest]

between/and didn't work either.

When I query only with a starting date, I get everything from that date
forward. When I query to/from, I get no error and a blank datasheet. I've
never used between/and. I'll try it and see if that makes any difference.

Jackie

It's done all the time, both as you've tried (>=, ... <=), and using the
expression "Between ... And ...".

If your attempt isn't working, what isn't working? Are you getting an error
message? Are you getting incorrect results?

More info, please...
Jeff Boyce
<Access MVP>

I have a date field in a 3rd party database where dates are stored as year,
month, day (i.e. May 6, 2005 is 20050506).

I have to enter the leading zero on single digit months or days to query
(no
biggie) BUT ... I can filter from this date forward (>=[Starting Date]),
but
not from one date to another (>=[Starting Date] And <=[Ending Date]).

What am I doing wrong? Surely it's possible to query to/from a date in
this
format, isn't it?

 

[Guest]

Are your end date months 10,11, or 12? Because they will technically be <
09, 08, etc. if this is a text field.

I am assuming that End Date and Start Date are parameters you enter when you
run the query.

Also why don't you actually convert this field to an actual date field?

between/and didn't work either.

When I query only with a starting date, I get everything from that date
forward. When I query to/from, I get no error and a blank datasheet. I've
never used between/and. I'll try it and see if that makes any difference.

Jackie

It's done all the time, both as you've tried (>=, ... <=), and using the
expression "Between ... And ...".

If your attempt isn't working, what isn't working? Are you getting an error
message? Are you getting incorrect results?

More info, please...
Jeff Boyce
<Access MVP>

I have a date field in a 3rd party database where dates are stored as year,
month, day (i.e. May 6, 2005 is 20050506).

I have to enter the leading zero on single digit months or days to query
(no
biggie) BUT ... I can filter from this date forward (>=[Starting Date]),
but
not from one date to another (>=[Starting Date] And <=[Ending Date]).

What am I doing wrong? Surely it's possible to query to/from a date in
this
format, isn't it?

 

[Jeff Boyce]

A couple other thoughts. You may have to set the type of the parameters in
the query.

And, as implied else-thread, your "dates" may be date/times.

What happens if you hard code a date range into your query criteria? Do you
get the records you expect?

Also, I just re-read what you're doing. If you have a text or number field
with 20050505, that is NOT, as far as Access knows, a date. If your query
is asking the user to supply a date, you are comparing apples and chainsaws.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Ok, here's where I show my ignorance ... how do I convert a field formatted
as 20050512 to a date field?

Are your end date months 10,11, or 12? Because they will technically be <
09, 08, etc. if this is a text field.

I am assuming that End Date and Start Date are parameters you enter when you
run the query.

Also why don't you actually convert this field to an actual date field?

between/and didn't work either.

When I query only with a starting date, I get everything from that date
forward. When I query to/from, I get no error and a blank datasheet. I've
never used between/and. I'll try it and see if that makes any difference.

:

Jackie

It's done all the time, both as you've tried (>=, ... <=), and using the
expression "Between ... And ...".

If your attempt isn't working, what isn't working? Are you getting an error
message? Are you getting incorrect results?

More info, please...
Jeff Boyce
<Access MVP>

I have a date field in a 3rd party database where dates are stored as year,
month, day (i.e. May 6, 2005 is 20050506).

I have to enter the leading zero on single digit months or days to query
(no
biggie) BUT ... I can filter from this date forward (>=[Starting Date]),
but
not from one date to another (>=[Starting Date] And <=[Ending Date]).

What am I doing wrong? Surely it's possible to query to/from a date in
this
format, isn't it?

 

[Guest]

Got it ... =DateSerial(Left([FieldName, 4), Mid(FieldName, 5, 2),
Right(FieldName, 2)). Thanks for your help.