Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DatePart Query 1

Status
Not open for further replies.

McFestoe

Technical User
Dec 16, 2003
145
GB
I think i may have done wrong setting up my db but its to late to change, i have 4 fields (text) service month 1 to 4, with the month in a drop down list as text, eg November. i have been trying to query these fields with date(m)) so i could set up a automated query to run on the 1st of the month.

Can access do this, what i mean is as i have store the month field as text can the query look at the current month with date() and pull all the records due the same month or do i need to change the text feild to a date feild.

For my database the service month would be best as a text feild, its easier to see on the screen than 01/11/07.

Any ideas or pointers.

Thanks
 
You confuse data storage and data display !
Store a date in a date field, period.
And display it as a formatted string:
Format([date field], "mmmm")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
In addition to PH's spot on comments, if you only need to store a month value, use a numeric field to store the values 1-12. Storing month names makes it more difficult to query month ranges.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Thanks guys lesson learnt, will have to look at changing db now or create a table for the month and link the query to it.

Gone to bury me head in the sand or read up.
 
Created a month table to hold jan = 1 feb = 2 and so on, my main form feilds for service month 1 to 4 now uses the month table to get the text value of the month.

Now iam running a query that looks at the month value 1 to 12 in the month table, but for this month its pulled 610 records when i know it should be 55.

SELECT Month.[Month Value], Month.Month, Table1.ID, Table1.Surname
FROM Table1, [Month]
WHERE (((Month.[Month Value])=Month(Now())));

I still need a value on my main for as text for the month as its easier to see December rather than 01/12/07.

Any more pointers.

 
Why do you need to use a table to look up "the text value of the month"? This is easy enough to calculate. If your Month Value contains numeric values 1-12 then use
MonthName([Month Value])
You can use a combo box on your main form for the month number selection:
[tt][blue]
Name: cboMonth
Control Source: [Field Storing Month Number]
Row Source Type: Value List
Row Source: 1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December
Column Count: 2
Column Widths: 0";1"
Bound Column: 1
List Rows: 12[/blue][/tt]



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Duane

Just what i needed, what a star.

Learnt something new with combo boxes, did not know they could hold many values always thought to do that they had to look at a table.

Many Thanks
 
Sorry,

but I'm a bit stuck on this. If you only want the month value and you store them as a numeric field, how do you then create a query to display them as January, February etc?

Dave
 
About the above, I tried Format([MyNumericMonth],"mmm") and it comes up as Jan, Jan, Jan etc.

It's strange because when I do Format([MyNumericMonth],"ddd") it works through the days of the week fine.

Does anybody know what the problem is?

Dave
 
Format(expression,"mmm") expects a date field as the expression, not a month number.

Your results basically convert 1 to 12/31/1899, 2 to 1/1/1900, 3 to 1/2/1900, etc. Numbers 2-12 will all have a month of January.

You should be able to use MonthName([MyNumericMonth])

Duane
Hook'D on Access
MS Access MVP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top