CODEWITHSUNDEEP
ms-accessms-access-2007criteria
Zahhar
Zahhar

Reputation: 187

Access 2007: Can I use Format() in query Criteria?

It seems I'm always trying to do the impossible. Well, I have a [month/day] table setup in the following format:

Noombers | Lemmas
-----------------
0101     | Jan 1
0102     | Jan 2
...      | ...
1230     | Dec 30
1231     | Dec 31

I have a table of [Publication Venues] with fields [Reads From] and [Reads To] that utilize this [month/day] table in that, when I enter the info, I just type in "Sep 1" and hit tab, which fills the field (in the background) with "0901". It's a text field because I want to sort alphanumerically and I also want to do an alphanumeric comparison. I thought I could do something like this in the Criteria for the query I've created for showing a list of publication venues that are currently reading:

Field:       Reads From              | Reads To
Table:       Publication Venues      | Publication Venues
Sort:       
Show:    
Criteria:    >Format(Date(),"mmdd")  | <Format(Date(),"mmdd")

But, though I am not seeing any errors, I am also not seeing the records that should be showing up. In fact, I'm not seeing any records.

So, is it safe for me to assume at this point that I can't use Format() in a query's Criteria? If so, then what is another way for me to go about setting up this filter? Using the table as I've already got it, preferably, since I've already got hundreds of records entered. If I can use Format(), then what do you suppose I'm doing wrong?

Here's the SQL:

SELECT
    [Publication Venues].*
FROM
    [Publication Venues]
WHERE
    ((([Publication Venues].[Still Active?])=Yes)
  AND
    (([Publication Venues].[Reads From])<Format(Date(),"mmdd"))
  AND
    (([Publication Venues].[Reads To])>Format(Date(),"mmdd")));

Upvotes: 0

Views: 2328

Answers (2)

Zahhar
Zahhar

Reputation: 187

This turned out to be the solution, at least for the way my brain works:

SELECT [Publication Venues].*
FROM [Publication Venues]
WHERE
   ((([Publication Venues].[Still Active?])=Yes)
AND
   (((([Publication Venues].[Reads From]) < ([Publication Venues].[Reads To]))
   AND
   (Format(Date(), "mmdd") BETWEEN ([Publication Venues].[Reads From]) AND ([Publication Venues].[Reads To])))
OR
   ((([Publication Venues].[Reads From]) > ([Publication Venues].[Reads To]))
   AND
   (Format(Date(), "mmdd") NOT BETWEEN ([Publication Venues].[Reads From]) AND ([Publication Venues].[Reads To]))))
);

I appreciate your help, people. Your feedback did get my mind working in a direction that lead me to a solution.

Upvotes: 1

Daniel
Daniel

Reputation: 13122

You see no errors because what you are doing does not cause any errors. However, it does not work as you expect.

If you are storying your dates as 4 numbers Access does not inherantly know how to interpret that. For instance, 1231 converted to a date, is May 15th 1903.

To use the format that you are using you would need to do >Format(Date()-40043,"mmdd") and <Format(Date()-40043,"mmdd") to make up for the difference.

More likely you'll want to convert your 4 digits into a date something like this:

Cdate(Left$([Reads From],2) & "/" & Right$([Reads From],2) & "/" & Year(Date()))>Date()

Long story short, you should store dates as dates if you want to use them as date. Otherwise you have to jump through some hoops so that your front end knows you're dealing with dates.

Upvotes: 1

Related Questions