Show report help please

[DigitalS27]

Hi guys,

I have created a small database for entering samples received and condition of the sample.

I then created a simple report and added a button on a form with a start date and end date field.

So on the form the user selects the customer from a combobox, the user can then enter a start date and end date or leave the date boxes blank. then when the user clicks show report it should show the report. However, I can't seem to get this right. when I select show report without selecting a customer from the combobox I get a messagebox " a customer needs to be entered" this is part of the code.

But then I select a customer, and then it asks me to enter the customer ID / enter parameter value for some reason. doesnt matter what customer id i enter, it gives a blank report and then access wants to debug.

Please, please assist.

Below is the code used:

Private Sub cmdPreview_Click()

   Dim stDocName As String
   Dim strWhere As String
   Dim Tmp As Date
   Dim dtStart As String
   Dim dtEnd As String

   stDocName = "SampleInformation"

   If Len(Trim(Me.cboCustomer)) > 0 Then
      strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "
   Else
      MsgBox " A customer is REQUIRED!!!"
      Exit Sub
   End If

   'check the dates
   If IsDate(Me.txtDateFrom) Then
      dtStart = Me.txtDateFrom
   End If

   If IsDate(Me.txtDateThru) Then
      dtEnd = Me.txtDateThru
   End If

   'create the filter string
   If IsDate(dtStart) And IsDate(dtEnd) Then
      If dtStart > dtEnd Then
         Tmp = dtStart
         dtStart = dtEnd
         dtEnd = Tmp
         Tmp = Empty
         Me.txtDateFrom = dtStart
         Me.txtDateThru = dtEnd
      End If
      strWhere = strWhere & "[Sample_Date] Between #" & dtStart & "# AND #" & dtEnd & "# AND "
   ElseIf IsDate(dtStart) Then
      ' greater than date Start
      strWhere = strWhere & "[Sample_Date] >= #" & dtStart & "# AND "
   ElseIf IsDate(dtEnd) Then
      'less than end date
      strWhere = strWhere & "[Sample_Date] <= #" & dtEnd & "# AND "
   End If

   If Len(strWhere) > 0 Then
      'remove the trailing " AND "
      strWhere = Left(strWhere, Len(strWhere) - 5)
   Else
      strWhere = ""
   End If

'      Debug.Print strWhere

   DoCmd.OpenReport stDocName, acPreview, , strWhere

End Sub

:banghead::banghead:

 

[CJ_London]

I've modified your post to put your code between the code tags so it is more readable. Please use code tags (the #) button going forward as it makes the code easier to read and you are more likely to get responses

 

[CJ_London]

you say customerID, but your code says

strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "

and if cboCustomer is a string and not an ID then you need to include quotation marks

strWhere = "[Customer_Number] = '" & Me.cboCustomer & "' AND "

 

[DigitalS27]

you say customerID, but your code says

strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "

and if cboCustomer is a string and not an ID then you need to include quotation marks

strWhere = "[Customer_Number] = '" & Me.cboCustomer & "' AND "

Sorry I meant customer number and not CustomerID.

I have tried what you suggested, but still get enter parameter value error.

 

[CJ_London]

so the parameter message asks you 'to enter the customer_number'

implies you do not have a field called customer_number in your report recordset.

Alternatively perhaps you have set report sorting or grouping on a field that no longer exists

 

[DigitalS27]

I have added a field customer_number to my report. but i still get the same error. can you please explain how i can edit this to remove the customer_number from my code?

 

[DigitalS27]

AFter I added the customer_number to the report, now when clicking the button to view the report I get the following error:

Data type mismatch in criteria expression

 

[CJ_London]

as mentioned before - you are trying to compare text and numbers

what is the rowsource to your combo?

 

[Pat Hartman]

Put a stop in the code on the OpenReport method. Display the contents of the where clause in the debug window and paste it here if you can't see what is wrong.

 

[DigitalS27]

as mentioned before - you are trying to compare text and numbers

what is the rowsource to your combo?

SELECT [dfs_Customers].[Customer_Number], [dfs_Customers].[Customer_Name] FROM dfs_Customers;

 

[CJ_London]

OK so other things to check

is the customer number field datatype text or number?
what is the bound column of your cboCustomer control?
provide the sql to your report recordset

it might be easier if you upload a copy of your app - remove anything confidential, compact and repair then zip. Go to the advanced editor on the forum for the upload facility

 

[arnelgp]

upload your db to better understand.

 

[DigitalS27]

Thank you!!

Will upload now.

There is no confidential information as this is a very small app that prototype for our lab to just enter sample information when samples are received. Currently they do it in excel

 

[DigitalS27]

Uploaded database

 

[CJ_London]

you said the cbocustomer rowsource was

SELECT [dfs_Customers].[Customer_Number], [dfs_Customers].[Customer_Name] FROM dfs_Customers;

what you actually have is

SELECT [Query1].[Customer_Name], [Query1].[Customer_Number] FROM Query1 ORDER BY [Customer_Name] DESC;

it needs to be the way you had it originally, just set the first column width to 0 to hide it

you also said your vba code is

strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "

when what you actually have is
strWhere = "[Customer_Number] = '" & Me.cboCustomerSel & "' AND "

you need to remove the single quotes.

For the future - please provide what you actually have, not something you think we want to see.

 

[DigitalS27]

y
For the future - please provide what you actually have, not something you think we want to see.

CJ, I did not provide something you want to see. I changed somethings this morning to try and get it working. I added a new combobox which I name cboCustomerSel as the other combobox was still on the form and could not give it the same name.

Also with the row source I tried using a manual query which I created this morning to see if that will help.

 

[arnelgp]

here is your report.