Acces query-criteria expression for several fields 1

[Jkm647]

I have a query that I want to pull all records which the field is called "RECORD DATE" with amounts in certain fields. My query has other fields, but here are the criteria fields

RECORD DATE week of 11/18 week of 11/24 week of 12/1
Between "1" and "4"
or Between "1" and "4" Between "1" and "4"


I have several date fields and I want to pull all the records that have information in the "week of" fields. I have 9 of those fields. Right now I only get the records with the date of 11/18. Thanks.

 

[fneily]

or Between "1" and "4" Between "1" and "4""

You have a OR Between... AND between...

You probably want just OR's so put them on different lines. If you're doing this in the QBE pane, after you type in the second OR, hit the down arrow. You'll see on the right you're scrolling down to the next line.

Across is AND, Down is OR.

Now, your table is not normalized. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You're violating the first normal form - repeating column headings. You said it yourself, week of, week of, etc. This will lead to variable length records, blank cells, etc. This can be a category like WeekOfEntry. So, your table show look like:
tblRecordDate
RDID Primary Key
WeekOfEntry
Amount
etc.

Now you don't have the problem you are facing. Normalizing your table will help with future problems you'll have.
tbl

 

[Jkm647]

That is fine, except I need 9 fields to say the Week of ??as I have to enter information for each week. So once the first week is over. I enter into the next week. What I can't figure out is why it is only pulling the one week. Thank you.
Each week I enter names into the first field
Then in the other Week of fields, I enter either 1, 2, 3, or 4. After that week is over, I don't use the Week of field for that week. But when I do a report, I need all names with all the week of entries. Thanks again.

 

[PHV]

why it is only pulling the one week
Could you, please, post the WHERE clause of your SQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Yeah, post your query and your table structure - example:
tblRecordData
RDID Primary Key
WeekOf11/18
WeekOf11/25
etc.

Also, maybe you didn't understand my explanation. You should not have 9 fields of dates. Just one.

 

[Jkm647]

Thanks to both of you for your help, I appreciate it, but somehow I was able to get it working.