Query Help

[Hunter O.]

Hello,
I have what is probably a simple problem that I just can’t solve. I have a table that has the date and time in one field that is formatted as General date. I then split the date and time using the Format() function that I found on this forum and it works great. I check the format on these fields and set them to short date and short time respectively. I would like to query the query by date that is splitting the table into separate date and time fields using criteria based of a calendar control this is referenced to a hidden text boxed on a form. This method has worked well in the past on other db where the date and time were enter in different fields in the tables from the start and didn’t need to be split. Any ideas what could be going on? I have attached the db I am testing on.

Thanks,
Hunter

 

[Mile-O]

To be honest, I'm struggling to find your question in your post.

And why do you have all those textboxes. You'll find it easier to use the calendar if you use it's AfterUpdate event. The ActiveX Calendar's events are not listed on the properties screen (i.e. the Updated event you are using is not useful here) but if you go into the VBA code editor and pick the calendar from the top left drop down and then use the top right dropdown you can see all its events.

 

[Hunter O.]

SJ McAbney
Sorry about the muddy question.
I can’t seem to query by date using the calendar like I have in the past on other db.
I would like to use the calendar to select the date as the criteria for the query.

Thanks,
Hunter

Edit

I am trying to use the calendar date as the criteria in the ThisDate field in the querysplitdateandtime.
I will also look into the AftetUpdate that you have mentioned. Again sorry for the poor question.

 

[Roo]

I think you need to substitute tblData.dateandtime in the query for a reference to the Calendar value on the form. At the time of the query running, have you actually written back the date selected to the tblData ? If not then it won't work.
Sorry if this sounds to simple.

 

[Mile-O]

Have a look at the attachment(s) here. They use the calendar and don't take up as much space on the form.

 

[Hunter O.]

Roo,

If I understand you, I need to reference the text box on the form that contains the date select on the calendar? If this is correct I have tried that without any success in this db.
My goal here is to be able to query tblData by date and not worry about the time portion in the field but I was unable to do that so that is when I split the field thinking it would be easier. I am finding out it isn’t.

SJ McAbney,

Thanks for the calendar links.

Thanks Again,
Hunter

 

[Roo]

Yep thats right, the source for the text box would be something like =Me.Calendar.Value (this can be a hidden text box of course), then you would use Forms!frmTBA!txtTBA.text (could be .value) in the query criteria. I have done this in the past.
Hope you get it working

 

[Mile-O]

then you would use Forms!frmTBA!txtTBA.text (could be .value) in the query criteria. I have done this in the past.

It wouldn't be the .Text property as you need the control you are referencing to have the focus for that. And you don't need .Value as it's the default value of a control.

 

[Hunter O.]

Thanks for the replies but I am starting to wonder is this possible?

Thanks,
Hunter