Range of dates not working in vba

[annmv888]

I have a form with multiple combo boxes that opens a report according to the values in my combo boxes. This works but I was asked to give an option to the Effective Date field so a date range can be entered. The Effective Date was imported and is a text data type which shows the data as yyyy-mm-dd.



I added two text boxes (txtReqCreationStartDate and txtReqCreationEndDate) for beginning and ending.

I tried doing the between...and in the query but then my combo boxes don't work.

I never used SQL but found some online for the Apply Filter on click button but I get all of the records. Others have tried to help but I always get all the records.

Here is all of the code for the On Click event.

Private Sub cmdVacanciesWithNoRequisitionParameters_Click()
On Error GoTo cmdVacanciesWithNoRequisitionParameters_Click_Err

strSQL = "SELECT * FROM qryVacanciesWithNoRequisition WHERE " _
       & "DateValue([Effective Date]) Between #" & Format([txtReqCreationStartDate], "yyyy-mm-dd") & "# And #" & Format([txtReqCreationEndDate], "yyyy-mm-dd") & "#;"

'Person Number
    If Not IsNull(Me.cboPersonNumber) Then
        strFilter = strFilter & " AND [Person Number] Like """ & Me.cboPersonNumber & """ "
    End If
    
             'Person Name
    If Not IsNull(Me.cboPersonName) Then
        strFilter = strFilter & " AND [Person Name] Like """ & Me.cboPersonName & """ "
    End If

             'Job Code
    If Not IsNull(Me.cboJobCode) Then
        strFilter = strFilter & " AND [Job Code] Like """ & Me.cboJobCode & """ "
    End If
 
            'If the report is closed, open the report
    If SysCmd(acSysCmdGetObjectState, acReport, "rptVacanciesWithNoRequisition") <> acObjStateOpen Then
        DoCmd.OpenReport "rptVacanciesWithNoRequisition", acPreview, qryVacanciesWithNoRequisition
    End If
    
        'if report was open, use filter
    With Reports![rptVacanciesWithNoRequisition]
        .Filter = Mid(strFilter, 6)
        .FilterOn = True
    End With
cmdVacanciesWithNoRequisitionParameters_Click_Exit:
    Exit Sub
cmdVacanciesWithNoRequisitionParameters_Click_Err:
    MsgBox Error$
    Resume cmdVacanciesWithNoRequisitionParameters_Click_Exit
End Sub

Thanks in advance for the help.

[ranman256]

you should be able to reformat the date to what works. mm/dd/yy or yy-mm-dd.
if this wont work, you can force the date to one that does by using another text box

txtDateAlt = mid(txtDate1,6,2) & "/" & right(txtDate1,2) & "/" & left(txtDate1,4)
then run your query off txtDateAlt

[ssanfu]

Would you post your dB?

[Micron]

DateValue uses your system settings if there are characters that it recognizes as date separators, so I believe you have to ensure system settings match the format of the date. That is, if your Windows Short Date setting is mm/dd/yyyy but your data is yyyy-mm-dd, then 2016 does not look like a month to Access. In trouble shooting, I'd debug.print your sql after the variables have been assigned, even plugging that into a new test query (if it looks right) to check the result. I'm not sure I'd use a combination of DateValue and Format functions, fearing the risk of not actually having two dates that have the same format. If the first suggestion doesn't uncover an issue, you could also try converting the string value to a date using CDate before applying a format.

[annmv888]

I tried to upload the file but received a message that "the upload failed". I wasn't given a reason so I don't know why it wouldn't work.

I am still getting all of the records.

[ssanfu]

Did you do a "Compact and Repair"? Then Zip it.
Zip max file size is 2 MB.


Or delete most records, then "Compact and Repair" and Zip it.

[annmv888]

I still can't upload this file. I deleted everything I'm not using. I have two tables with 5 records each, the query, the form and the report. I removed everything I could from the form and report. I did a compact and repair and it's still too big. It's 600 KB and only 500 KB is allowed.

I just tried creating a empty database and importing all the objects...that worked. It's 496KB.

I did comment out the code I have been given while I was trying different things. I was given a way to do this by using strFilter instead of strSQL. The strFilter code ran but I received an "undefined function 'value' in expression" error.

[Micron]

Did you zip the file before posting as instructed? If not and you'd prefer the larger dB then zip and try again.

[annmv888]

Sorry, I forgot.

[Micron]

There's a lot about this that I don't see making sense. For your posted question, the issue I see is that you created a filter query to open the report on, but put no date criteria in it. IMO, you should reference the form controls as criteria in the query date fields, but I don't know which of the 3 date fields you'd use. Perhaps add Req Create Date from your table to the query and reference the form controls. I can't elaborate at the moment as the db is no longer responding in design view.
There's a lot of disconnected info (e.g. you have creation start and end dates, but there's no apparent relationship between this and the underlying query or report.

edit: working again. I may be writing about the wrong form - I didn't see any posted instructions as to which form to open and started guessing based on parts of your postings.
It was reqs with no parameters.
edit2: further reading, I opened Vacancies with no requisition form and input two dates; got one record in the report, which corresponds to what the query returns if I manually put those dates in the query. Worked for me. Are you entering the date in the same format as the db expects, or are users inputting 05/05/2016 when the field contains 2016-05-05??

[annmv888]

That was the very first thing I did but then my combo boxes didn't work because it always wanted the dates to be entered. If they were left blank I don't get anything. I need to be able to pass the startdate and enddate into the code just like I do for the entry selected in a combo box. It does do that for the strFilter but it doesn't like the DateValue function. I think the biggest problem is [Effective Date] is a text data type. I tried to import it and convert it to datetime data type but received an error message for every record.

[annmv888]

Sorry, I forgot to tell you which one when I zipped it and sent everything. It was the Vacancies with no req. I can see data too if I type it in the query but I have to have the flexibility to choose anything from the form and give either give it a range or leave it blank. I have been typing it the way it is listed in the table which is 2016-05-05.

[annmv888]

I got it to work. This is what I needed to be part of my code. It will also work in combination with the other combo boxes I have.

If Not IsNull(txtReqCreationStartDate) And Not IsNull(txtReqCreationEndDate) Then
strFilter = strFilter & " AND [Effective Date] Between """ & Me.txtReqCreationStartDate & """ And """ & Me.txtReqCreationEndDate & """"
End If

[Micron]

I also started with creating a filter portion for dates, but all this code is really unnecessary, so I came up with this approach:
1) dump this sql into a new query to see the calculated field and criteria expression

SELECT tblImportVacanciesByPerson.[Person Number], tblImportVacanciesByPerson.[Vacancy ID], tblImportVacanciesByPerson.[Person Name], tblImportVacanciesByPerson.[Action code], tblImportVacanciesByPerson.[Action Reason], CDate([Effective Date]) AS Expr1, tblImportVacanciesByPerson.[Business Unit], tblImportVacanciesByPerson.Department, tblImportVacanciesByPerson.Supervisor, tblImportVacanciesByPerson.[Job code], tblImportVacanciesByPerson.[Job Title]
FROM tblImportVacanciesByPerson LEFT JOIN tblAllVacanciesWithRequisitions ON tblImportVacanciesByPerson.[Vacancy ID] = tblAllVacanciesWithRequisitions.VACANCY_ID1
WHERE (((CDate([Effective Date])) Between [forms]![frmVacanciesWithNoRequisitionParameters].[txtReqCreationStartDate] And [forms]![frmVacanciesWithNoRequisitionParameters].[txtReqCreationEndDate]) AND ((tblAllVacanciesWithRequisitions.VACANCY_ID1) Is Null))
ORDER BY tblImportVacanciesByPerson.[Vacancy ID];

2) format the two date controls as short date and use the calendar to force users to pick a system formatted date (for me this is mm/dd/yyyy)
3) test your new query with different dates when the form is in form view (seemed to work for me). When working, you can set the other query fields to reference the form controls in the same way, allowing you to remove all the coded filters
4) you'll have to set the report recordsource to this revised query and remove any filters you're applying

The query should filter against the chosen dates without having to worry about trying to format them. It's my belief that the issues are thus:
- you cannot compare the data by converting a string date using DateValue to a string that has simply been reformatted to "look" like a date. It's my belief that formating a date data type is one thing, but applying a format to a date string does not convert it to a date data type.

Aside from that, try to learn how to use the same form to open one report (or other form) using queries as filters, or by applying a filter to the report/form being opened rather than have a one-to-one relationship between them. You should be opening such objects in this manner rather than creating multiple objects in order to see different data sets.

[ssanfu]

After looking at your dB, I feel I can not help any further. IMO, there is too much wrong with the dB and I don't have time to help you develop it.

I do recommend you read:
Fundamentals of Relational Database Design
http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Then work through the tutorials at
http://www.rogersaccesslibrary.com/forum/forum46.html



I'm not trying to be mean, but I think you are in for a lot of work because of the current structure.
Good luck with your project......