query criteria

[Guest]

I'm using a query based on an imported table which uses two fields for date.
I do not control and cannot change the data format of the table before it
comes to me from another source.
One field [entrymo] uses the numerical value for the month 1=Jan and the
other [entryyr] uses the 4 digit year. The data spans more than one year.
I am trying to build a criteria which allows users to enter a range of
dates, but am not sure how to format it in a way which makes sense to a user.
If I use a "Between this and that".....criteria for each field I run into
trouble when searching between years. Trying to search between 1 2004 and 1
2005, I returned only records from 1 2004 and 1 2005, not the range.
I realize it is because the criteria is based on two different fields. In
effect I was asking the query to return [entrymo] records between 1 and 1 AND
[entryyr] records between 2004 and 2005.
This will become a real problem as data is populated for 2005. Any ideas
how to restructure the criteria?
I hope this is clear.

 

[Barry Jon]

Hi Rob,

Try this;

PARAMETERS [Start Date (mm/yyyy)] DateTime, [End Date (mm/yyyy)] DateTime;
SELECT TableName.entrymo, TableName.entryyr
FROM TableName
WHERE (((DateSerial([entryyr],[entrymo],1)) Between [Start Date (mm/yyyy)]
And [End Date (mm/yyyy)]))
ORDER BY TableName.entryyr, TableName.entrymo;

What you want to do is create a calculated column (not displayed) on which
to base your between statement. The DateSerial function seems ideal as when
given a year, month and day it returns a date. The day doesn't appear to
matter so I simply chose 1 in the example sql.

Hope this helps.

Regards

Barry-Jon

 

[Guest]

Thanks. I'm not sure I did all you mentioned correctly so it's not quite
working.
In my query, I added a column I named [combined date] and used the
DateSerial function to convert the [entrymo] and [entryyr] fields to one
date. Worked great.
This is where i think I missed something you said.
I then added my "between statement" to the criteria of column [combined date].
However, when I ran the query, it still returned the same results as before.
If I run the query without the "between statement", then I get all the
records and the [combined date] field converts the date just as it should.
Should I have entered statements or expressions elsewhere?

Hi Rob,

Try this;

PARAMETERS [Start Date (mm/yyyy)] DateTime, [End Date (mm/yyyy)] DateTime;
SELECT TableName.entrymo, TableName.entryyr
FROM TableName
WHERE (((DateSerial([entryyr],[entrymo],1)) Between [Start Date (mm/yyyy)]
And [End Date (mm/yyyy)]))
ORDER BY TableName.entryyr, TableName.entrymo;

What you want to do is create a calculated column (not displayed) on which
to base your between statement. The DateSerial function seems ideal as when
given a year, month and day it returns a date. The day doesn't appear to
matter so I simply chose 1 in the example sql.

Hope this helps.

Regards

Barry-Jon

I'm using a query based on an imported table which uses two fields for
date.
I do not control and cannot change the data format of the table before it
comes to me from another source.
One field [entrymo] uses the numerical value for the month 1=Jan and the
other [entryyr] uses the 4 digit year. The data spans more than one
year.
I am trying to build a criteria which allows users to enter a range of
dates, but am not sure how to format it in a way which makes sense to a
user.
If I use a "Between this and that".....criteria for each field I run into
trouble when searching between years. Trying to search between 1 2004 and
1
2005, I returned only records from 1 2004 and 1 2005, not the range.
I realize it is because the criteria is based on two different fields. In
effect I was asking the query to return [entrymo] records between 1 and 1
AND
[entryyr] records between 2004 and 2005.
This will become a real problem as data is populated for 2005. Any ideas
how to restructure the criteria?
I hope this is clear.

 

[Barry-Jon]

Rob

When in the query design grid choose 'Query' > 'Parameters...' from the
menu bar. In here define a date from and date to paramter (query
variable) of date type (e.g. [Date From] under the paramter column,
'Date/Time' under the Data Type column). In your between statement you
should use the exact parameters you have defined as above. (e.g. [Start
Date] And [End Date]). You shouldn't have the users enter their start
and end months and start and end years serperately. I found this
worked when I tried it entering my start from and end from in the
format mm/yyyy. It didn't work when I entered the short year format.
Let me know how you get on because we could refine it so they could
enter start and end month and start and end year if we really had to.

Regards

Barry-Jon

 

[Guest]

I was able to get it to work. Thank you so much for your patient help!
There is one remaining wrinkle. Don't know if we can resolve it this way.
When first designed, I had built a form in which the user was able to enter
the date ranges. The original query criteria pulled the user entry from the
form fields and filtered the records that way.
Obviously, it is important to tell Access to treat this info as a date. Is
there a way to still use the query parameter and still control the user entry
from the form?

 

[Barry-Jon]

Hi Rob,

Yes you can do this. I mocked it up with Form1 with text boxes
txtDatefrm & txtDateto. The query would then look like this:

PARAMETERS [Forms]![Form1]![txtDatefrm] DateTime,
[Forms]![Form1]![txtDateto] DateTime;
SELECT TableName.entrymo, TableName.entryyr
FROM TableName
WHERE
(((DateSerial([entryyr],[entrymo],1))>=[Forms]![Form1]![txtDatefrm] And
(DateSerial([entryyr],[entrymo],1))<=[Forms]![Form1]![txtDateto]));

As you can see you just declare the parameter as being the control on
the form. Though I am sure you already know the form has to be open
for this query to work. Good luck and let me know how it goes

Regards

Barry-Jon

 

[Guest]

It WORKS!. I can't believe it. Thank you, Thank you.
Ironically, it was the last tip that did it. I had theorized that it was
possible to add the form fields to the parameter, but had not opened the form
before running the query, so it didn't work.
Thanks again, especially for not assuming that I knew better.