DatePart Query 1

[McFestoe]

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

 

[PHV]

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

 

[dhookom]

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]

 

[McFestoe]

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.

 

[McFestoe]

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.

 

[dhookom]

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]

 

[PHV]

Furthermore, have a lookhere:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

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

 

[McFestoe]

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

 

[Dave177]

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

 

[Dave177]

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

 

[dhookom]

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

 

[Dave177]

Thanks Duane!