Using Inputbox date in Select statement (1 Viewer)

[OnlyTD]

Hi
I have a module where I want to filter the results based on dates input by the user but the variable is not recognised can you tell me what I am doing wrong please?

Basically
Dim FiltStartDate As String

FiltStartDate = InputBox("Start Date?")
‘Error catch
If FiltStartDate = "" Then
MsgBox "You have not supplied any information"
End If
If IsDate(FiltStartDate) Then
FiltStartDate = Format(CDate(FiltStartDate), Date)
Else
MsgBox "This isn't a Date"
End If

Then I need to filter the data for my report to be greater than FiltStartDate using the line beow
Select ***
From ***
WHERE (((tbl_Bookings. Start_Date)>=FiltStartDate And ((tbl_Bookings.End_Date)<=FiltEndDate))) "

Through the debug I can see the information in FiltStartDate but access does not recognise it and keeps asking for FiltStartDate (I will be setting up the FiltEndDate when this works).

Thank you

 

[theDBguy]

Hi. Try changing this line a little bit...

[FONT=Calibri][SIZE=3]FiltStartDate = Format(CDate(FiltStartDate), "\#yyyy-mm-dd\#")

[/SIZE][/FONT]

 

[OnlyTD]

Hi thedbGuy

Thank you, this does look much better but when I run it it is still asking me for FlitStartDate when it reaches the 'Where' line even though the date is in FiltStartDate above.

 

[theDBguy]

Hi thedbGuy

Thank you, this does look much better but when I run it it is still asking me for FlitStartDate when it reaches the 'Where' line even though the date is in FiltStartDate above.

Oh, sorry, I missed that part. Try this part next...

..." [SIZE=3][FONT=Calibri]WHERE (((tbl_Bookings. Start_Date)>=[B]" & FiltStartDate & "[/B] And ((tbl_Bookings.End_Date)<=FiltEndDate))) "[/FONT][/SIZE]

PS. Hey, I just noticed there's a FiltEndDate in there as well. You may have to do the same thing we're doing with FiltStartDate if it's doing the same thing (giving you a prompt for it).

 

[gemma-the-husky]

Dates have to be surrounded by ## characters. Therefore, your expression needs to look like this

"WHERE tbl_Bookings.Start_Date >= #" & FiltStartDate & "# And tbl_Bookings.End_Date <= #" & FiltEndDate & "#"

so the final string looks similar to this
WHERE tbl_Bookings.Start_Date >= #04/01/2019# And tbl_Bookings.End_Date <= #04/30/2019#


Now in this syntax your dates FiltStartDate and FiltEndDate will be treated as US dates, so if you aren't in the US, you will have a problem.

eg , in the UK #01/04/2019# will end being processed as Jan 4th, not 1st April.

The DBGuy's fix offers a formatting structure to deal with this.

 

[Cronk]

Alternatively create and save a query

Select *** From *** WHERE tbl_Bookings.Start_Date>=[What is the Start Date?]  And tbl_Bookings.End_Date<=[What is the End Date?]

 

[Solo712]

Hi
I have a module where I want to filter the results based on dates input by the user but the variable is not recognised can you tell me what I am doing wrong please?

Basically
Dim FiltStartDate As String

FiltStartDate = InputBox("Start Date?")
‘Error catch
If FiltStartDate = "" Then
MsgBox "You have not supplied any information"
End If
If IsDate(FiltStartDate) Then
FiltStartDate = Format(CDate(FiltStartDate), Date)
Else
MsgBox "This isn't a Date"
End If

Then I need to filter the data for my report to be greater than FiltStartDate using the line beow
Select ***
From ***
WHERE (((tbl_Bookings. Start_Date)>=FiltStartDate And ((tbl_Bookings.End_Date)<=FiltEndDate))) "

Through the debug I can see the information in FiltStartDate but access does not recognise it and keeps asking for FiltStartDate (I will be setting up the FiltEndDate when this works).

Thank you

I think your first problem is that you are trying to do the testing of a date variable on a string. You should define FiltStartDate as Date and obtain by converting the input box variable which is Variant. After you convert it, you should write the SELECT statement with the pragmas as gemma-the-husky said...something like this:

Dim FiltStartDate As Date, iDate as Variant

iDate = InputBox("Start Date?")
‘Error catch
If iDate = "" Then
MsgBox "You have not supplied any information"
End If
If IsDate(iDate) Then
FiltStartDate = CDate(iDate)
Else
MsgBox "This isn't a Date"
'Exit Sub
End If

'
"Select ***
From ***
WHERE tbl_Bookings. Start_Date)>= #" & Format(FiltStartDate, "mm/dd/yyyy")  & "# AND tbl_Bookings. End_Date)<= #" & _
Format(FiltEndDate, "mm/dd/yyyy") & "#"

Good luck!
Jiri

 

[OnlyTD]

Hi All

Thank you so much for your replies - they have all taught me more and helped me to understand what I am working with.

I am using the where statement from Jiri because I also need to use those dates a little later on but I am getting a syntax error and I have tried all ways but it still occurs.

& "WHERE (tbl_Bookings.Start_Date >= #' & Format(FiltStartDate, 'dd/mm/yyyy') & '# And (tbl_Bookings.End_Date>=#' & Format(FiltStartDate, 'dd/mm/yyyy') & '# " _

Thank you

 

[Minty]

You are missing closing brackets and your date formats are incorrect, try

WHERE (tbl_Bookings.Start_Date >= #' & Format(FiltStartDate, 'yyyy-mm-dd') & '#) And (tbl_Bookings.End_Date>=#' & Format(FiltStartDate, 'yyyy-mm-dd') & '#) "

It's often easier to create your filter as as separate string the debugging becomes easier

Dim sWhere as String

sWhere = "(tbl_Bookings.Start_Date >= #' & Format(FiltStartDate, 'yyyy-mm-dd') & '#) And (tbl_Bookings.End_Date>=#' & Format(FiltStartDate, 'yyyy-mm-dd') & '#) "
debug.print sWhere

 

[gemma-the-husky]

you are mixing quote characters. you have unmatched brackets

do this, then you can inspect the string

wherestrg =  "WHERE tbl_Bookings.Start_Date >= #" & Format(FiltStartDate, "dd/mm/yyyy") & "# And tbl_Bookings.End_Date>= #" & Format(FiltStartDate, "dd/mm/yyyy") & "# " 

msgbox wherestrg

it should look like this

WHERE tbl_Bookings.Start_Date >= #01/04/2019# And tbl_Bookings.End_Date>= #30/04/2109#

... except that this will treat the from date as Jan 4th, not 1st April.

 

[OnlyTD]

Hi all

Thank you this is now working fine I am so greatfull!!

One last question to finish it off....sorry

When the dates have been input by the user and the report figures generated the report is opened in preview. How do I pass the dates input by the user into a text box on the report?

Thank You

 

[theDBguy]

Hi all

Thank you this is now working fine I am so greatfull!!

One last question to finish it off....sorry

When the dates have been input by the user and the report figures generated the report is opened in preview. How do I pass the dates input by the user into a text box on the report?

Thank You

Hi. What did you end up using? If a parameter prompt, you can duplicate the same prompt in the report. If a form control, then you can use a form reference in the report.

 

[Minty]

Ditto the DBguy (Faster tying than me !)

Use a form to get the dates and then you use them in both the query and the report.

 

[OnlyTD]

Hi

I put the format line at the beginning so that I only used it once (the dates are used further down in the function too)
STDate:
sDate = InputBox("Start Date?")
'Error Catch
If sDate = "" Then
MsgBox "You have not suplied any information"
Goto STDate
End If

If IsDate(sDate) Then
FiltStartDate = Format(sDate, "dd/mm/yyyy")
Else
MsgBox "This isn't a Date"
GoTo STDate
End If

EnDate:
eDate = InputBox("End Date?")

'Error Catch
If eDate = "" Then
MsgBox "You have not suplied any information"
End If

If IsDate(eDate) Then
FiltEndDate = Format(eDate, "dd/mm/yyyy")
Else
MsgBox "This isn't a Date"
GoTo EnDate
End If

If FiltEndDate < FiltStartDate Then
MsgBox "Your end date must be later than the start date"
GoTo STDate
End If
FiltEndDate = Format(eDate, "dd/mm/yyyy")

Then I used:
& "WHERE tbl_Bookings.Start_Date >= #" & FiltStartDate & "# And tbl_Bookings.End_Date>= #" & FiltEndDate & "# " _

I was planning to have a button that ran the code and opened the report directly, it works but I want to add the dates used to my report to.

 

[theDBguy]

Hi

I put the format line at the beginning so that I only used it once (the dates are used further down in the function too)
STDate:
sDate = InputBox("Start Date?")
'Error Catch
If sDate = "" Then
MsgBox "You have not suplied any information"
Goto STDate
End If

If IsDate(sDate) Then
FiltStartDate = Format(sDate, "dd/mm/yyyy")
Else
MsgBox "This isn't a Date"
GoTo STDate
End If

EnDate:
eDate = InputBox("End Date?")

'Error Catch
If eDate = "" Then
MsgBox "You have not suplied any information"
End If

If IsDate(eDate) Then
FiltEndDate = Format(eDate, "dd/mm/yyyy")
Else
MsgBox "This isn't a Date"
GoTo EnDate
End If

If FiltEndDate < FiltStartDate Then
MsgBox "Your end date must be later than the start date"
GoTo STDate
End If
FiltEndDate = Format(eDate, "dd/mm/yyyy")

Then I used:
& "WHERE tbl_Bookings.Start_Date >= #" & FiltStartDate & "# And tbl_Bookings.End_Date>= #" & FiltEndDate & "# " _

I was planning to have a button that ran the code and opened the report directly, it works but I want to add the dates used to my report to.

Hi. Thanks for the clarification. So, you're not using either of the ones I mentioned. Also, I'm surprised the code you're using works. Try entering a date like 1/5/2018 and 4/5/2018 and see what you get.

In any case, I think Minty's suggestion to use a form would be the best approach for you. Otherwise, you might end up using the OpenArgs argument to pass the dates when you open the report and then use code in the Open event of the form to read the dates.

 

[OnlyTD]

i think I have done this, I added this code at the bottom:

DoCmd.OpenReport "RptSummary_Report", acViewReport
Report_RptSummary_Report.txtStart_Date = FiltStartDate
Report_RptSummary_Report.txtEnd_Date = FiltEndDate

 

[theDBguy]

i think I have done this, I added this code at the bottom:

DoCmd.OpenReport "RptSummary_Report", acViewReport
Report_RptSummary_Report.txtStart_Date = FiltStartDate
Report_RptSummary_Report.txtEnd_Date = FiltEndDate

Yes, that works too! What about the "little test" I suggested, did you try it?

 

[OnlyTD]

Hi
Thank you DBGuy, I thought I was just modifying what you suggested, can you tell me the difference in the date formats and which is the safest to use please?

So far I have a button to run the code and placed the following at the end after the docmd.Openreport :

Report_RptSummary_Report.txtStart_Date = FiltStartDate
Report_RptSummary_Report.txtEnd_Date = FiltEndDate


Which seems to work but any suggestions are welcome..please

 

[theDBguy]

Hi
Thank you DBGuy, I thought I was just modifying what you suggested, can you tell me the difference in the date formats and which is the safest to use please?

So far I have a button to run the code and placed the following at the end after the docmd.Openreport :

Report_RptSummary_Report.txtStart_Date = FiltStartDate
Report_RptSummary_Report.txtEnd_Date = FiltEndDate


Which seems to work but any suggestions are welcome..please

Hi. The part about displaying the dates are fine, but I would still tend to agree with Minty that using a form for date inputs would make it easier to display it on the report. What I was asking you to do is double-check your code for filtering the data with the user inputted dates. Did you try what I asked? I said to try and input dates like Start: 1/5/2018 and End: 4/5/2018. Did it result in the correct data in your report? If you don't have any records within those dates, try entering a couple of test records within those dates and then try the test again. I'll explain the reason why after you've ran the test. You might be able to figure it out after seeing what happened. I think you would learn better if you experience the issue yourself. Besides, I could be barking at the moon and there's really nothing to worry about. Your test should tell us for sure.

 

[OnlyTD]

Hi DBGuy

I am assuming that I would look at these results in the immediate window as the report does not show the dates of the each result but I dont know how?