Using Inputbox date in Select statement (1 Viewer)

[gemma-the-husky]

the trouble is that if you do this

DoCmd.OpenReport "RptSummary_Report", acPrint 'not sure about the syntax
Report_RptSummary_Report.txtStart_Date = FiltStartDate
Report_RptSummary_Report.txtEnd_Date = FiltEndDate

then the report will print before the date range gets populated, and you will probably get an error. as well

One way is to have the report date range built into the query (it will appear the same on every row) and then you can just bind the query field to the report control to fill the dates.

 

[Minty]

Your report has a data source (probably a query), you should be able to go into the design of the report and open that just like a query to see the reports underlying data.

 

[theDBguy]

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?

Hi. All I'm trying to ascertain from you is if your code, which you said works, is actually working. So, I was simply asking you to run it as normal and when it asks you for the start and end dates before the report opens, to simply enter the dates I suggested (I can suggest other dates as well). So, after the report opens, it is not important (for this simple test) to actually see the dates you entered. I am more interested in finding out if the records showing on the report (if any) are the correct records you would expect to see given the dates you used to open the report. Make sense? So, were they (the correct records)?

 

[OnlyTD]

Hi

Basically my function does a few different things and uses several tables as follows:
Table A emptied
Table B emptied
Table C emptied
Qry A created and appended to table A
Qry B created and appended to table B
Qry C created and appended to table C
Contents of table A, B, C sent to Summary table
Open Report based on Summary Table

Qry A is the one I have been asking about.

 

[theDBguy]

Hi

Basically my function does a few different things and uses several tables as follows:
Table A emptied
Table B emptied
Table C emptied
Qry A created and appended to table A
Qry B created and appended to table B
Qry C created and appended to table C
Contents of table A, B, C sent to Summary table
Open Report based on Summary Table

Qry A is the one I have been asking about.

Hi. I am assuming you were responding to my post. What I was asking you to verify is if your report is showing you the correct records. If it does, then I guess you're all good. Thanks.

 

[OnlyTD]

as sson as I have fixed the syntax error on the information below I will check the dates but I have changed the criteria slightly because the results I need have to be:

Bookings that are courses, not with exam in the title, not with C as the status ref, and with a specific location, between specified dates.
I have used the following (taking the Sql from a query) but again I am stuck with a syntax error, is there any information where I can read up so I can understand these a bit better?

& "WHERE (((tbl_Booking_Instance.Booking_Type) Like ' * Course * ') AND " _
& "((tbl_Booking_Instance. Course_Event_Title) Not Like ' * Exam * ') AND " _
& "((tbl_Booking_Instance.Status_Ref) NOT LIKE ' * C * ') AND " _
& "((tbl_Booking_Instance.Course_Location)='Gartholwg Lifelong Learning Centre') AND " _
& "(tbl_Booking_Instance. Start_Date)>= #" & FiltStartDate & "# And (tbl_Booking_Instance.Start_Date <= #" & FiltEndDate" _

Sorry

 

[theDBguy]

as sson as I have fixed the syntax error on the information below I will check the dates but I have changed the criteria slightly because the results I need have to be:

Bookings that are courses, not with exam in the title, not with C as the status ref, and with a specific location, between specified dates.
I have used the following (taking the Sql from a query) but again I am stuck with a syntax error, is there any information where I can read up so I can understand these a bit better?

& "WHERE (((tbl_Booking_Instance.Booking_Type) Like ' * Course * ') AND " _
& "((tbl_Booking_Instance. Course_Event_Title) Not Like ' * Exam * ') AND " _
& "((tbl_Booking_Instance.Status_Ref) NOT LIKE ' * C * ') AND " _
& "((tbl_Booking_Instance.Course_Location)='Gartholwg Lifelong Learning Centre') AND " _
& "(tbl_Booking_Instance. Start_Date)>= #" & FiltStartDate & "# And (tbl_Booking_Instance.Start_Date <= #" & FiltEndDate" _

Sorry

Hmm... I thought, earlier, you said you were already done and the code works. To troubleshoot the syntax error, do a Debug.Print and then copy and paste the result in the query designer to let Access highlight the error area for you.

 

[Minty]

You haven't completed the final date with "#" on the end.

Although not a syntax error, your Like clauses all appear to have extra spaces between the leading wildcard character , the string and the trailing wildcard. This will possibly lead to incorrect results.

Have a look at this sample which will hopefully sort this out for you https://www.access-programmers.co.uk/forums/showthread.php?t=293372

It converts a query from the SQL in design view to the VBA equivalent.

 

[OnlyTD]

Hi

yes DBGuy I had it working but then I had to add another filter and which is when I started recieving the Syntax error again

The link for the converter looks really good thankyou Minty.

I will be working on this again on Tuesday, and hopefully I will be able to test the dates then. I will keep you updated.

 

[theDBguy]

Hi

yes DBGuy I had it working but then I had to add another filter and which is when I started recieving the Syntax error again

The link for the converter looks really good thankyou Minty.

I will be working on this again on Tuesday, and hopefully I will be able to test the dates then. I will keep you updated.

Sounds good. Have a nice weekend!

 

[Cronk]

I use forms when getting date ranges from users. However if you base your report on the following query, it not only prompts for the start/end dates but makes them available as available fields for your report

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