Help with Dates

[Tom]

Hi all,

I have a table with field name "ConnectionDate".
I want to create a form with ComboBox that will show the "ConnectionDate" as
month and year only.
The value the user will choose will be a criteria for a query.

Please help.

TIA,

Tom

 

[Klatuu]

?Format(Date,"mmyyyy") would return 112007

so,
SELECT Format(ConnectionDate,"mmyyyy") AS ConnectDate FROM SomeTable

 

[Tom]

Thank you so much, that was helpful.
But when I choose 112007 the query do not work. The format in the table is
dd\mm\yyyy.

Any suggestion?

Thanks,

Tom

 

[Klatuu]

Is the table field a datetime data type or a string?
There is no reason the query should not work if it is a datatime data type.
The Format is not formatting the value in the table, only in the output of
the query.
Post back the SQL of the query and the data type of the field, please.

 

[Tom]

Just to be clear,

When user choose 112007 I would like the query to pull all records that
stamped in November 2007.

Thanks,

Tom

 

[Ron2006]

The question is then

How are you expressing the comparison/criteria for this query.?

To get it to work you have to somehow (and there are multiple ways)
have the query parse the month and year of the field of the search
query/table to match the format of the criteria you are sending it.

Ron

 

[Klatuu]

How do you know you want November 2007?
Is it in a text box on your form or are you wanting all records for the
current month?

 

[Tom]

Thanks for your replies.
In the table the date format is yy/mm/yyyy. I created a form with ComboBox
which show the date from that table in mmyyyy format. (i.e 112007).
In the query I put thet ComboBox value as criteria.
Basically, if a user choose 082007 then all records from August 2007 should
be pulled out or 102007 will show all records from October 2007.

Maybe the way I set it up is not ideal, if you have any other way to acheive
this goal, please let me know.


I hope I',m clear here.

Thanks,

Tom

 

[Tom]

Here is the SQL:
SELECT Controltbl.ConnectionID, Controltbl.ConnectionDate,
Controltbl.Category
FROM Controltbl
WHERE
(((Controltbl.ConnectionDate)=([Forms]![DateSearch]![ConnectionDate])));

ConnectionDate is the ComboBox

TIA,
Tom

 

[Tom]

The format in the table is mm/dd/yyyy and not dd/mm/yyyy as I mentioned
before.

Thanks,
Tom

 

[Ron2006]

I do not believe that that type of comparison will work.

you are asking it to compare something that is formated as mm/yyyy
(and is not therefore a real date) to a date field.

Remember a date field is really a number field, for which the interger
part is the number of days since and the decimal part is a number
representing a time. Therefore the real value in the date field is a
series of numbers with a range of 30 different values for the month of
november. And you are giving something that is a unique value. It will
not work.

In the query define a field called
matchmonth:month(Controltbl.ConnectionDate) and another one called
matchyear: year(Controltbl.ConnectionDate)

Then for the criteria for each respectively put month([Forms]!
[DateSearch]![ConnectionDate])

and year([Forms]![DateSearch]![ConnectionDate])

Changeing the format of the way a date is seen does not change the
underlying value.

Ron

 

[Tom]

Thanks.