DH Query By Forms Problems

  • Thread starter Thread starter tbd
  • Start date Start date
T

tbd

Hi!

I recently downloaded Duane Hookums' QBF database from
Rogers Access Library, and I have been editing it slightly
to apply to my own database. Everything so far has ran
smoothly (the Database is excellent!) but I am having
problems filtering dates on the Between parameter when I
go to create a new report. In my database, the date field
I am attempting to filter on only contains the month and
year e.g. 01/2004. I would like to specify a report that
would contain information for dates between 01/2004 and
05/2004, but the database does not return this
information. It's as if it does not recognise the field
as a date format.

I tried getting around this by using the Format Function
to actually specify the field as a date format, but this
has had no effect. Also, when I select the date field
when creating a new report, the automatic delimeter is ',
rather than #, which again makes me think the DB doesn't
recognise the field as a date field. Has anyone got any
ideas, or Duane if you are out there please help!!

TBD.
 
tbd said:
Hi!

I recently downloaded Duane Hookums' QBF database from
Rogers Access Library, and I have been editing it slightly
to apply to my own database. Everything so far has ran
smoothly (the Database is excellent!) but I am having
problems filtering dates on the Between parameter when I
go to create a new report. In my database, the date field
I am attempting to filter on only contains the month and
year e.g. 01/2004. I would like to specify a report that
would contain information for dates between 01/2004 and
05/2004, but the database does not return this
information. It's as if it does not recognise the field
as a date format.

I tried getting around this by using the Format Function
to actually specify the field as a date format, but this
has had no effect. Also, when I select the date field
when creating a new report, the automatic delimeter is ',
rather than #, which again makes me think the DB doesn't
recognise the field as a date field. Has anyone got any
ideas, or Duane if you are out there please help!!

TBD.
Click to expand...

Hi, TBD. I'm not familiar with Duane's QBF database, but I can tell you
that, as far as Access is concerned, there is no such thing as a Date
field that only contains month and year. "Date" is a data type that
always contains a complete date and time, even if one or the other is
not displayed (the default time is midnight). From your description,
I'd say that your date field is really a field of type Text. You may be
storing month and year values in it, and even applying date *formats* to
it, but it's still just a text field.

It seems to me that you have three options:

1. Separate out the month and year into two separate fields in your
tables, then apply more complex criteria on both fields to achieve the
necessary filtering.

2. Use expressions to transform your existing date fields into true
date-time fields on the fly and apply your criteria to the converted
fields. Note that CDate("01/2004") = #1/1/2004# and CDate("05/2004") =
#5/1/2004#.

3. Change your date field into a true Date/Time field. If you always
enter it in the form mm/yyyy, the day part of the field will always be
1. You'll have to use appropriate formatting when displaying the field
to show just the month and year.

I don't know how well these options fit into Duane's QBF techniques.
Maybe he'll pop in here and give some advice.
 
Thanks very much Dirk for sending me in the right
direction. I have it fixed now, thanks for your help!!

TBD
 
Back
Top