Selecting by date and time

[Luongo1]

I have two seperate fields in my access database - one for the date and one for the time. Would it be possible to use both of these together for the purposes of selecting and sorting records in an access report? For example, have the user specify start and end points for both date and time (ie April 2, 3:00pm to April 3, 5:40pm). Can a report query be built to perform this, or would the two fields have to be combined? Thanks for any help you can provide,

George

 

[dhookom]

You can add the two fields together as if they were numbers (since they are).

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]

 

[Luongo1]

Hi Duane, thanks once again for your reply. However I'm not sure in what sense you mean the fields should be added.

Here's a piece of my database query, as posted in an earlier thread:

WHERE [Communication Log].Entered Between Forms![Generate Report]!BeginDate And Forms![Generate Report]!EndDate

Now say I add two fields - BeginTime and EndTime. Would I be able to insert these values into the above query, so that the records between BeginDate, BeginTime and EndDate, EndTime are displayed?

 

[dhookom]

So are BeginTIme and EndTime fields in [Communication Log]?

Are you trying to determine if a duration occurs entirely between Forms![Generate Report]!BeginDate And Forms![Generate Report]!EndDate?

Are you trying to add the times to the values from your form controls?

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]

 

[Luongo1]

I'm using a form where the user specifies the start points and end points of the records he or she wants on the report. Each record has a date field as well as a time field in the Communication Log table. However, up until this point, the user has only been able to specify the dates from the form, whereas now they need to be able to specify more exact start and end points - in others words the date as well as the time.

I can add time fields on the form, but I'm not sure how to query for these records because the date and time are seperate fields, so if the user wanted records from, say, April 15 at 5:00pm to April 18 at 11:00pm I would have to somehow query both the date and time fields to come up with these results. Sorry, I'm not great at explaining it but I hope this answers your questions.

Thanks,
George

 

[dhookom]

You can add the "date field as well as a time field" and allow users to enter the date and time into the form text boxes. Your query would then have a where clause like:
[tt][blue]
[DateField]+[TimeField] between Forms![Generate Report]!BeginDate And Forms![Generate Report]!EndDate[/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]

 

[Luongo1]

Hi Duane,

Here's what I'm trying, based on your suggestion:

[Communication Log].Entered+[Communication Log].Time Between Forms![Generate Report]!StartTime And Forms![Generate Report]!EndTime

StartTime and EndTime are then something like 02-Apr-07 1805, which is the same format in which these values are stored in the database. However, I've played around with different formats and it never seems to produce any results, despite the fact that there should (theoretically) be some. Is there some other way I could/should be entering this info?

Thanks again for your help,
George

 

[dhookom]

Can we assume that both Entered and [time] are date/time fields? BTW: Time is a poor name for a field since Time is a function. You should always place brackets around Time if you mean to reference a field. Is your Time field actually a long integer? Is it really storing the value 1805 rather than 18:05:00?




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]

 

[Luongo1]

Yes, Entered and Time are both date/time fields. As for the Time field, it is in the format of hnn, so it displays 1805 in the table, but then when clicked on shows 6:05:00 PM. I have adjusting the field values to both of these but nothing seems to be working.

 

[dhookom]

I find this confusing "I have adjusting the field values to both of these but nothing seems to be working." What are you adjusting?

What are some stored dates and times as well as the values you have used in Forms![Generate Report]!BeginDate And Forms![Generate Report]!EndDate?

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]

 

[Luongo1]

Hi Duane,

The stored times are values such as 615, 1145, 1805, etc. Therefore I've tried using values formatted like this in Forms![Generate Report]!BeginDate and EndDate but this does not work in producing any results. I also tried entering different formats in the form such as 6:15:00 AM but these also do not work.

 

[dhookom]

If you time field is a date/time data type and you are storing values of 615, 1145, and 1805 then your date/time values are 9/6/1901, 2/18/1903, and 12/9/1904. A date/time field stores the number of days

You might be see these values but you should be actually storing values like 0.260416, 0.489583, and 0.753472. Don't be confused by what is stored and what is displayed.

You haven't provided any actual date and time field values and the two values you have entered into the form. Unless you do this, we can't be of much help. You should also try widen your date range to about 10 years in the future as part of your trouble-shooting.

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]