Acces Query Trouble

[Marvinator]

i'm in the middle of a database and need some help with Queries. I have a field that is a DATE field, and I want to be able to write a query that will pull out those records by MONTH by inputing the month in question. When I click on the Query, I want there to be an Input where I put in the 1 or 2 digits representing the month and then hit [enter] to bring up the list.

I've got the input sytax: Like [Type Month] - but this will not pull out just the month.

I've tried using wild cards, but it still gives me sytax errors. how would you write this query?

[vass0922]

use DATEPART function

Extract part of existing date values using a calculated field
In query Design view, create a query. Add the tables whose records you want to use.


Use the DatePart function in an expression in an empty cell in the Field row in the query design grid.
The syntax for this function is:

DatePart(datepart,date)

The datepart argument is the abbreviation for the part of the date you want returned. Examples are "yyyy" for a four-digit year and "q" for a calendar quarter. The date argument is either a field name with the Date/Time data type or a literal date such as "7-Nov-93".

The following table lists examples of expressions that apply the DatePart function to the OrderDate field from the Orders table. If the value in
OrderDate is
This expression
Returns
3-Jun-93 DatePart("m",[OrderDate]) 6 (month of year)
28-Mar-92 DatePart("yyyy",[OrderDate]) 1992 (four-digit number of year)

[Marvinator]

This makes sense, however I want to be able to input WHICH month I am looking up. I want to call up a list of all items brought in during themonth of (example) June. so, when I click the Query, it should ask me for the month.

does this make sense?

[vass0922]

Yes you'll have to create a form, then in the criteria reference that form...

its something like

Criteria: DATEPART("m", [ColumnName]) = formname1!controlname
or something like that, you may have to look up the exact syntax

[Marvinator]

I've been playing with this for weeks. Syntax is not my strong suit. now, I can't seem to get this one thing to work, and at the same time, I'm getting way too many records that don't reall exist. i'm not sure why, but it's not working.

[vass0922]

what is the criteria you have now?
cut and paste it into here.

Before you bother with the form, just hardcode a number for the month... once you get that working then bother with the form.

[Marvinator]

Well, I don't need or want a form. I want a list, a data sheet.

for the field, I did as you stated.
Datepart("m",Tapes Received.Date) ;Tapes REceived is the name of the table.

for Criteria I have the following
Like [Type month]

Now this seems to work well, until I try to get more information than is in the Tapes Received Table. I also want the CITY and CONTACT LAST NAME from the main Contact Info Table. I have a relationship set up where the main part of both tables is the COUNTY. Now, when I try this out with just the information from the Tapes Received table, it seems to work. When i try to add in the City and Contact last name, it gives me every last name in the list connected to the only two counties in the Tapes Recieved Table.

[vass0922]

You can't have spaces in table names or column names ... ALWAYS put the names in [brackets] if they have spaces

It sounds like you have a bad join that isn't valid.

Are you saying you join a contacts table and a "tapes received" table ... on a county?!?!
So you're saying there will never be more than one contact in one county?

so, when I click the Query, it should ask me for the month.

If you want this functionality you will need a small form TMK