Query Criteria Questions - 2

[MsNaniB]

Good Morning Everyone! Here are my questions....

1.) I would like to be able to pull up invoices that only have months July through December. Is there a "through" expression that I can use in the criteria? The problem I'm having is that I need to use words, "January", and not dates "01/2011".

2.) I have a query that shows all my "Actuals" finance information, which is tied to a "Budget Number" table so that it brings up everything, including budget number. My problem is that it's ONLY giving me invoices that have a budget number, so it's leaving the other "Actuals" info out. How can I fix this so that my query shows everything from my Actuals table, even if it doesn't have a budget number tied to it?

Thank you!
Nani

 

[plog]

1. There is a IN expression and a BETWEEN expression that may help you. The real key is how is the date on the invoices stored--is as text ("January") or is it as dates (1/2/2010)? Could you provide a sample of the data in the table you want to query?

2. For this you want a LEFT JOIN. In query design view you left click on the line that links your two data sources and change it so it brings in all data from your Actuals table and just the matching records from your other data source.

 

[MsNaniB]

Hi Plog! Thank you so much for your help!

The data is stored as "Text" since I manually typed in the words, "January", "February" and so on...
I'm thinking I may have to change all of that to numbers 01, 02, etc. If I don't have to, I really don't want to...lol I realize that asking Access to give me a "between" of 2 "words" is probably asking too much.
My Table looks like this:

Budget Table
Budget# / Month / Year / Account
where Month = Text

My query looks like this:

Query #1
Budget# / Month / Project

So instead of seeing every month from Jan-Dec, I want to be able to pick a range, if possible. Otherwise I know I'd have to type which months I want to see in the criteria box, which is too time consuming.

Thanks Plog!

P.S. - Query issue #2 is working! Thank you!

 

[plog]

Glad to hear #2 is resolved. For #1 I would recommend changing your month data to numbers. Otherwise, instead of running from January to December your year is going to be in alphabetical order (April, August, December, etc.).

After converting to numbers, when you want to include multiple months in your query you would put something like this in the criteria under your month data:

>=7 AND <=12

That would return months July - December. If you used text you would have to explicitly state each month you want to include.

Another benefit is that numbers are quicker to enter and harder to mistype than text ('2' vs. 'February', 'Feb', 'Febuary').

 

[MsNaniB]

I gotcha, Plog! I was afraid of that..lol...thank you!