PDA

View Full Version : Need Help With Pulling In Dates From 2 Different Fields (Access 2013)


hesykhia
03-23-2016, 10:09 AM
Hey guys! So I'm trying to figure out the best way to write some VBA code for a query/report.

I have a form where there are boxes where a user can select dates, and then if you click the button above it, it opens a report that has records that are between those two dates. Here is the code I'm using for that:



DoCmd.OpenReport "BENsReportCassieM", acViewReport, , "[DateWorked1st] >= #" & Forms!CassieMTotalsForm!BENsSDate & "# And [DateWorked1st] <= #" & Forms!CassieMTotalsForm!BENsEDate & "#"


The issue I'm having is, there is a second field I need it to look at as well, a second date (DateWorked2nd). So, it needs to either be within the selected date range for "DateWorked1st" AND/OR "DateWorked2nd". Is there a way to add this into the code, or will I break it? Is this even the best way to go about this?
hesykhia
03-23-2016, 10:10 AM
A side note, the "Forms!CassieMTotalsForm!BENsSDate" and "Forms!CassieMTotalsForm!BENsEDate" are the start and end date boxes on the form. They need to kind of stay as wildcards because we'll need a different date range each time the report is run.
jonh
03-23-2016, 11:06 AM
http://www.w3schools.com/sql/sql_between.asp

([mydatefield1] between dt1 and dt2) OR ([mydatefield2] between dt1 and dt2)
hesykhia
03-23-2016, 12:14 PM
I put that in, and it pulls up asking for a parameter value for DateWorked2nd. The record source for the report is a union query. I don't know if that makes a difference or not?
jonh
03-23-2016, 01:46 PM
You put what in?
hesykhia
03-23-2016, 01:47 PM
DoCmd.OpenReport "BENsReportCassieM", acViewReport, , "([DateWorked1st] >= #" & Forms!CassieMTotalsForm!BENsSDate & "# And [DateWorked1st] <= #" & Forms!CassieMTotalsForm!BENsEDate & "#) OR ([DateWorked2nd] >= #" & Forms!CassieMTotalsForm!BENsSDate & "# And [DateWorked2nd] <= #" & Forms!CassieMTotalsForm!BENsEDate & "#)"
jonh
03-23-2016, 02:08 PM
If a query asks for a parameter value and it isn't a parameter query you've either spelled the field name wrong or the field isn't in the query.
jonh
03-23-2016, 02:13 PM
Also, you really are better off using BETWEEN, because then it doesn't matter if start date is less than end date or not, the query works it out, meaning less need for validation.