Hi all,

I need to remove the time input from the DTPicker. i am using it as part of a data entry form and added it sometime after i created the form.

However, i now find that for any entries which were made using the DTPicker, the queries I run on the entered data does not pick up the entries made by the DTPicker because of the date format.

Is there anyway to remove the time? Or is there a way to design the query so it picks up entries made with the DTPicker?

help much appreciated.

regards

Keji

Hi
Check out the DateValue() commad to remove your time value.
Access: DateValue Function.

Hi Larry,

thanks for that, sorry but i'm still learning,so im unsure as to where to put it.

in the query, (which is querying a table which some of the DateOccured field entries were done manually and some done by the DTPicker), the DateOccured field has a parameter based on the date entry from a form (which uses the DTPicker as well).

so do i specify the date time value in the in the field or the field parameter for the query?

i don't even know if any of that makes sense?

help!

cheers

Keji

Hi
I'm stiil unsure how your using your DTPicker but where ever the date picked value used enclose it wihin the DateValue() function and also remember make sure both your criteria and data are of the same format.

Hi again Larry,

here is what the SQL code for my query looks like

PARAMETERS [Forms]![frmChooseDTDate]![DateOccured] DateTime;
SELECT Downtimes.DateOccured, Downtimes.ShiftID, Downtimes.LineID, Downtimes.MachineID, Downtimes.MaterialCode, Downtimes.DTCategoryID, Downtimes.HoursLost, Downtimes.DTReasonID, Downtimes.CorrectiveActionID
FROM Downtimes
WHERE (((Downtimes.DateOccured)=[Forms]![frmChooseDTDate]![DateOccured]));

im not sure how the DateValue function fits in there

The DTPicker is used at to enter the date into tblDowntimes which is the table being queried. It is used again to select the date parameter to run the query through frmChooseDate.

hope that makes it clearer.

cheers!

Keji

Hi
If your datepicker is [DateOccured] which is both date & time values then just enclose it in the DateValue()
eg:
WHERE (((Downtimes.DateOccured)=[Forms]![frmChooseDTDate]!DateValue([DateOccured])));

thanks for that but Im now getting an error dialog box after having done that, it says

Undefined function '[Forms]![frmChooseDTDate]!DateValue' in expression

any ideas?

regards

Keji

Hi
Hmm.. a little unsure what's going on here.. are you able to post up a cut down version..?

the important forms are

frmChooseDTDate (this is the form where you select the date you want to use as the parameter for the query)

you willl notice that if you enter any date prior to 16/03/2006, you will return results for the query (qryDaily DT Events) which will open the form frmDaily DT Events

however if you enter anything post 16/03/2006, you will not get anything (anything in the tbl Downtimes after this date was entered using the DTPicker, events with earlier dates were entered manually)

ive removed the DateValue from the query so you can see it running.

thanx for having a look.

i wasnt sure how best to cut it down, i removed a lot of the data in downtimes but left some so u can run the query and i zipped it to reduce the size

regards

Keji

Hi again,

thanks for your help Larry, I just tried this to see if it would work

WHERE ((DateValue(Downtimes.DateOccured)=[Forms]![frmChooseDTDate]!DateValue([DateOccu
red])));

...and it did! the problem appears to be resolved, and the query now recognises the entries that were made using the date picker

thaanks sooooooo much for your help!!

regards

keji