Runtime Error 3071 When Used With IF Statement to Highlight Incorrect Entry

[Damo1412]

Hi,

In my database I am trying to produce a "Statistics" function. As part of this, the user will enter a "Start Date" and "End Date" in a form and then click a button which will open the requested report with the date drawn from a query. The code on clicking the "All Jobs" button is:

DoCmd.OpenReport "RepStatisticsAllJobs", acViewPreview

Which works perfectly.


I am trying to stop the user from leaving the date fields blank or entering dates outside of the range of the database so I have tried the code:

Private Sub AllSalesEnquiries_Click()
If Me.DateFrom = "" Then
    Beep
    If MsgBox("You have not entered a start date", vbCritical, "Start Date Not Entered") Then
    Exit Sub
    End If
ElseIf Me.DateFrom <#24/03/2014# Then
    Beep
    If MsgBox("The database does not contain records before 24th March 2014", vbCritical, "Start Date Too Early") Then
    Exit Sub
    End If
ElseIf Me.DateTo = "" Then
    Beep
    If MsgBox("You have not entered an end date", vbCritical, "End Date Not Entered") Then
    Exit Sub
    End If
ElseIf Me.DateTo > Date() Then
    Beep
    If MsgBox("You cannot search beyond today's date", vbCritical, "End Date After Today") Then
    Exit Sub
    End If
End If
DoCmd.OpenReport "RepStatisticsAllJobs", acViewPreview
End Sub

If the user enters dates within the range of the database the report is presented correctly.

If the user does not enter a date or enters one outside of the range it produces the correct message box however if the user then corrects the mistake I receive a Runtime Error 3071 message. Clicking "Debug" highlights the final line of code:

DoCmd.OpenReport "RepStatisticsAllJobs", acViewPreview

I know that the code is correct because it works fine as stand alone code and it works if the user enters the correct dates so I am not sure where I am going wrong.

Just for clarification, I am British and the date format works perfectly for the way dates are formatted in the database.

Thanks in advance for your help.

 

[namliam]

dates in code always are in US formats, thus your 24/03 will not work... it should be 03/24

Also your dataentry box is most likely a text box that people type dates in, so that is causing an issue.

Also you may need to check for NULL values instead of just ""

 

[Damo1412]

Hi namliam,

With regards to the dates, they are selected by the user from a text box in the form, I didn't know there was another way to do that. I've checked the results of query using the dates as they are and the results are correct so I don't think that is causing the problem.

Please excuse my ignorance but how would I incorporate a check for NULL values and not just "". I tried

If Me.DateFrom = "" Or IsNull Then

and

If Me.DateFrom = "" Or IsNull() Then

but I keep receiving error messages.


I don't know if this will hep or not but I tried changing the last line of the code (which calls up the report) to a simple message box and the code worked every time. I changed the last line from:

DoCmd.OpenReport "RepStatisticsAllJobs", acViewPreview

to

MsgBox "This has worked"

This worked both when the correct dates were entered the first time and also when they were entered incorrectly and then amended.

 

[jdraw]

You may find the examples here useful.

Good luck.

 

[Minty]

You can overcome some of the problems by making default form values for your DateFrom and DateTo fields.
I normally default DateFrom to the first day of the current month

=Date()-(Day(Date())-1)

and DateTo as Date().

Null - Checking - Nearly but you need to do this;

If Me.DateFrom = "" Or IsNull(Me.Datefrom) Then ...

You should check that on the form after update to prevent an incorrect entry and restrict entry to after your data start point.

Your report error is probably caused by calculations on the dates when they are incorrect. Can you post up the reports data source (SQL Query)?

 

[Damo1412]

Hi Minty,

Thanks for the IsNull advice. I've added the "IsNull" and that seems to have partially resolved the problem.

With some messing around, I think that the problem does lie in the query and I THINK I know where, I'm just not sure what I need to do to change it.

On the form, the user has to select a "date from" and a "date to". If they only want to see statistics for one client during the date range, they can select the client from a combo box.

On the form, I have a "Clear Data" button. Clicking on this button reverts the "DateFrom", "DateTo" and "ClientCombo" fields to "". I think that this is what is causing the issue. If I select a client after clearing the data it works fine however if I leave the client blank I get the error message.

I have tried running the query and that is definitely where the error is. I think that the problem is in the criteria for the client. This is the SQL of the query

SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsEnquiryDate, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, Jobs.JobsClientJobNumber, SiteDetails.SiteDetailsAddressLine1, Jobs.JobsCompanyDetailsName, Jobs.JobsCompanyDetailsAddressLine1, Jobs.JobsCompanyDetailsAddressLine2, Jobs.JobsCompanyDetailsAddressLine3, Jobs.JobsCompanyPostCode, Jobs.JobsCompanyDetailsTelephone, Jobs.JobsCompanyDetailsFax, Jobs.JobsCompanyDetailsEmail, Jobs.JobsCompanyDetailsWebsite, Jobs.JobsCompanyRegistration
FROM SiteDetails RIGHT JOIN (ClientDetails RIGHT JOIN Jobs ON ClientDetails.ClientDetailsURN = Jobs.JobsClientURN) ON SiteDetails.SiteDetailsURN = Jobs.JobsSiteURN
WHERE (((Jobs.JobsEnquiryDate) Between [Forms]![Statistics]![DateFrom] And [Forms]![Statistics]![DateTo]) AND ((ClientDetails.ClientDetailsURN)=[Forms]![Statistics]![ClientCombo])) OR (((Jobs.JobsEnquiryDate) Between [Forms]![Statistics]![DateFrom] And [Forms]![Statistics]![DateTo]) AND (([Forms]![Statistics]![ClientCombo]) Is Null))
ORDER BY Jobs.JobsSalesEnquiryRecordNumber;

Thanks in advance

 

[Minty]

This bit of your query doesn't make much sense to me

... AND (([Forms]![Statistics]![ClientCombo]) Is Null))

You are checking that your combo box is null in the query rather than setting a criteria, which I think would cause you a problem.

I would set an after update on the ClientCombo selection that if it's null prevents the report running. You can then remove the troublesome second part of the query code.

 

[namliam]

"" is not the same as NULL, again you need to capture both seperately

 

[Damo1412]

Thanks Minty and namliam,

I'm not sure where that extra line in the query came from. I've recreated the query and am still having problems but namliam has found where I am going wrong. The SQL of the recreated query is

SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsEnquiryDate, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, Jobs.JobsClientJobNumber, SiteDetails.SiteDetailsAddressLine1, Jobs.JobsCompanyDetailsName, Jobs.JobsCompanyDetailsAddressLine1, Jobs.JobsCompanyDetailsAddressLine2, Jobs.JobsCompanyDetailsAddressLine3, Jobs.JobsCompanyPostCode, Jobs.JobsCompanyDetailsTelephone, Jobs.JobsCompanyDetailsFax, Jobs.JobsCompanyDetailsEmail, Jobs.JobsCompanyDetailsWebsite, Jobs.JobsCompanyRegistration
FROM SiteDetails RIGHT JOIN (ClientDetails RIGHT JOIN Jobs ON ClientDetails.ClientDetailsURN = Jobs.JobsClientURN) ON SiteDetails.SiteDetailsURN = Jobs.JobsSiteURN
WHERE (((Jobs.JobsEnquiryDate) Between [Forms]![Statistics]![DateFrom] And [Forms]![Statistics]![DateTo]) AND ((ClientDetails.ClientDetailsURN)=[Forms]![Statistics]![ClientCombo] Or [Forms]![Statistics]![ClientCombo] Is Null))
ORDER BY Jobs.JobsSalesEnquiryRecordNumber;

Currently the "Clear Data" button changes the "ClientCombo" field to "" which is causing the problems.

Which is the best way for me to resolve this? Should I add something to the criteria meaning that it will treat "" as Null and if so what would I add or should I change the "Clear Data" button to perform a different action? It's current action is

Me.DateFrom = ""
Me.DateTo = ""
Me.ClientCombo = ""
Me.Refresh

I did try changing the code to

Me.DateFrom = ""
Me.DateTo = ""
Me.ClientCombo = Null
Me.Refresh

in the hope that changing its value to Null would work with the criteria in the query but this still does not work.

I appreciate that this is now a query problem however I do appreciate your continued assistance

 

[namliam]

Well dealing with NULL values in your query like you are doing is a bad overall idea IMHO.

Instead you should make your query dynamic.

Something like

dim MySQL as string
mySQL = "SELECT Jobs.JobsSalesEnquiryRecordNumber " & _
             ", Jobs.JobsEnquiryDate " & _
             ", ClientDetails.ClientDetailsURN " & _
             ", ClientDetails.ClientDetailsName " & _
             ", Jobs.JobsClientJobNumber " & _
             ", SiteDetails.SiteDetailsAddressLine1 " & _
             ", Jobs.JobsCompanyDetailsName " & _
             ", Jobs.JobsCompanyDetailsAddressLine1 " & _
             ", Jobs.JobsCompanyDetailsAddressLine2 " & _
             ", Jobs.JobsCompanyDetailsAddressLine3 " & _
             ", Jobs.JobsCompanyPostCode " & _
             ", Jobs.JobsCompanyDetailsTelephone " & _
             ", Jobs.JobsCompanyDetailsFax " & _
             ", Jobs.JobsCompanyDetailsEmail " & _
             ", Jobs.JobsCompanyDetailsWebsite " & _
             ", Jobs.JobsCompanyRegistration " 
mysql = mysql & _  
       " FROM       SiteDetails " & _  
       " RIGHT JOIN (ClientDetails " & _  
       " RIGHT JOIN Jobs           ON ClientDetails.ClientDetailsURN = Jobs.JobsClientURN) " & _  
       "                           ON SiteDetails.SiteDetailsURN = Jobs.JobsSiteURN " & _  
       " WHERE      Jobs.JobsEnquiryDate Between [Forms]![Statistics]![DateFrom] " & _  
       "                                     And [Forms]![Statistics]![DateTo] "

If NZ([Forms]![Statistics]![ClientCombo], "") <> "" then
' Assuming ClientDetailsURN is a number
    mySQL = mysql & " AND ClientDetails.ClientDetailsURN)= " & [Forms]![Statistics]![ClientCombo] & " " & _
' for a text field
'    mySQL = mysql & " AND ClientDetails.ClientDetailsURN)= """ & [Forms]![Statistics]![ClientCombo] & """ " & _
' For a date field, remember that the combo then must be in US format
'    mySQL = mysql & " AND ClientDetails.ClientDetailsURN)= #" & [Forms]![Statistics]![ClientCombo] & "# " & _

endif
Mysql = mysql & " ORDER BY Jobs.JobsSalesEnquiryRecordNumber; "

this deals with the non-filled value the way it should be dealt with .... If no data is intered in the Combobox, you dont want to use it in the query.

 

[Damo1412]

Thanks namliam,

Even though I am not an SQL programmer (as you might have gathered) you have made that so straight forward even I can understand it.

Thanks again for all of your help