show record between 2 date in qury (1 Viewer)

theinviter · Oct 17, 2019

hi guys

need help

i have qurey and wanna to show record between 2 date (start date and end date) but the data entered in the field contain date and time. so is there i way to show the record by entering the start date in one field and time in another filed and End date in new filed and Time in another filed and then filter the record based on date and time entered (between).

tried this code but just filter date;
start date = >=[Forms]![Patient_Information]![Date_from] Or Nz(<=[Forms]![Patient_Information]![Date_from],"")=""

End date = <=[Forms]![Patient_Information]![date_To] Or Nz(<=[Forms]![Patient_Information]![date_To],"")=""

how to make a code for time.

theDBguy · Oct 17, 2019

Hi. If you're saying the table data contains time components, then you can simply add some time components to your criteria as well. For example, ...Between [StartDate] And [EndDate] + #23:59#

theinviter · Oct 17, 2019

table data contain
Start date = Now
End date = now +Duration

the form contain a report filter
Date_from= Date
Time_from = Time
Date_To = Date
Time_To = Time

so wanna filter the report query by data entered by user in above field.

theDBguy · Oct 17, 2019

theinviter said:
table data contain
Start date = Now
End date = now +Duration

the form contain a report filter
Date_from= Date
Time_from = Time
Date_To = Date
Time_To = Time

so wanna filter the report query by data entered by user in above field.

As I was saying, you could try something like.

Code:

... WHERE FieldName Between Date_From + Time_From AND Date_To + Time_To

Please remember to use proper Form references, if necessary.

Gasman · Oct 17, 2019

Can you not just add Time to Date?

Code:

? timevalue(now())
21:27:47 
? cdec(timevalue(now()))
 0.894722222222222 
? cdec(date)
 43755 
? cdec(now())
 43755.8951967593

theinviter · Oct 17, 2019

theDBguy said:
As I was saying, you could try something like.

Code:

... WHERE FieldName Between Date_From + Time_From AND Date_To + Time_To

Please remember to use proper Form references, if necessary.

i tried this code in query ( Start date ) but dose not show any report

Between [Forms]![Patient_Information]![date_from]+[Forms]![Patient_Information]![Time_From] And [Forms]![Patient_Information]![date_to]+[Forms]![Patient_Information]![Time_to]

theDBguy · Oct 17, 2019

theinviter said:
i tried this code in query ( Start date ) but dose not show any report

Between [Forms]![Patient_Information]![date_from]+[Forms]![Patient_Information]![Time_From] And [Forms]![Patient_Information]![date_to]+[Forms]![Patient_Information]![Time_to]

Hi. You forgot to specify the field you're trying to filter.

theinviter · Oct 17, 2019

theDBguy said:
Hi. You forgot to specify the field you're trying to filter.

which field , i have 2 field in the query
1- start date
2- End date

should i put the code in 2 field or what
what should i do .

theDBguy · Oct 17, 2019

theinviter said:
which field , i have 2 field in the query
1- start date
2- End date

should i put the code in 2 field or what
what should i do .

Hi. Since we can't see your actual query, we're having a hard time picturing what you want. Can you post your SQL statement and maybe some images? Also, can you describe in plain words what you want your query to do? Thanks!

theinviter · Oct 17, 2019

theDBguy said:
Hi. Since we can't see your actual query, we're having a hard time picturing what you want. Can you post your SQL statement and maybe some images? Also, can you describe in plain words what you want your query to do? Thanks!

please see the attached screen

theDBguy · Oct 17, 2019

theinviter said:
please see the attached screen

Hi. Thanks for that. How about the English description of the requirement, as I asked? For example, If the user enters all the search requirements, namely, the start and end dates and times, what did you want the query to return?

theinviter · Oct 17, 2019

theDBguy said:
Hi. Thanks for that. How about the English description of the requirement, as I asked? For example, If the user enters all the search requirements, namely, the start and end dates and times, what did you want the query to return?

once the user enter the date and time then the query will show only record that are between start date + time and end date + time.

theinviter · Oct 17, 2019

theDBguy said:
Hi. Thanks for that. How about the English description of the requirement, as I asked? For example, If the user enters all the search requirements, namely, the start and end dates and times, what did you want the query to return?

i tried this code on Star date and working good but when i do the same on END date then dose not show any record .

>=(CDate([Forms]![Patient_Information]![Date_from])+CDate([Forms]![Patient_Information]![Time_From]))

theDBguy · Oct 17, 2019

theinviter said:
once the user enter the date and time then the query will show only record that are between start date + time and end date + time.

Hi. Not quite. Okay, given the user can request for records between two dates, what will qualify a record to be within these dates? Since you have two fields in your table, start and end, how does the criteria apply to them? For example, do you want all records where start is within the user input? Or, do you want all records where end is within the input data? Or something else? Perhaps there confusion because the names of your date fields are the same as the criteria input on the form.

theinviter · Oct 17, 2019

theDBguy said:
Hi. Not quite. Okay, given the user can request for records between two dates, what will qualify a record to be within these dates? Since you have two fields in your table, start and end, how does the criteria apply to them? For example, do you want all records where start is within the user input? Or, do you want all records where end is within the input data? Or something else? Perhaps there confusion because the names of your date fields are the same as the criteria input on the form.

yes i want all records where start and end are within the user input

theDBguy · Oct 17, 2019

theinviter said:
yes i want all records where start and end are within the user input

Hi. Either I am just being too slow today or maybe I'm tired because it's almost quitting time, but I still don't get what you're asking. Your table has two date fields. So, if the user enters a time period, which of the two fields did you want to fall within said time period?

theinviter · Oct 17, 2019

theDBguy said:
Hi. Not quite. Okay, given the user can request for records between two dates, what will qualify a record to be within these dates? Since you have two fields in your table, start and end, how does the criteria apply to them? For example, do you want all records where start is within the user input? Or, do you want all records where end is within the input data? Or something else? Perhaps there confusion because the names of your date fields are the same as the criteria input on the form.

thank you dear i have solved the issue as i will filter by start date only will serve the purpose

theDBguy · Oct 17, 2019

theinviter said:
thank you dear i have solved the issue as i will filter by start date only will serve the purpose

Ah, excellent! Glad to hear you got it sorted out. Sorry for being so dense. Good luck with your project.

Gasman · Oct 18, 2019

theinviter said:
please see the attached screen

Surely you should be comparing startdate in table to start date in form and likewise end date in table with end date in form, plus the form time elements.?

The BETWEEN is used when you are comparing one date from source against two dates required.

show record between 2 date in qury (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Enthusiastic Amateur

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

I’m here to help

Registered User.

Registered User.

Attachments

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Enthusiastic Amateur

Similar threads

Users who are viewing this thread