ACCESS - Need to tweak 2 lines of code to cater for Numerical Data

[barton]

Here is the total code.

Dim strGenre As String
    Dim strPlatform As String
    Dim strFilter As String
    'if the value in this combo box is null, then show all records
    If IsNull(Me.cboGenre.Value) Then
        strGenre = "Like '*'"
    Else
        strGenre = "='" & Me.cboGenre.Value & "'"
    End If
     'if the value in this combo box is null, then show all records
    If IsNull(Me.cboPlatform.Value) Then
        strPlatform = "Like '*'"
    Else
        strPlatform = "='" & Me.cboPlatform.Value & "'"
    End If
    'combining criteria to form a WHERE clause
strFilter = "[Genre] " & strGenre & " AND [Platform] " & strPlatform
    
    With Reports![rptTopRented]
         .Filter = strFilter
         .FilterOn = True
    End With

The bit I want to change to allow the numerical data is:

Else
        strPlatform = "=' " & Me.cboPlatform.Value & " ' "
    End If

The combo box names 'cboPlatform' is a list of Number ID's. And since currently its interperated as text, I've been told to maybe adjust or take out the qualifiers (I dont know what a qualifier is) in that bit of code to cater for numerical data.

Also, I have a code, where, I want select a field (which has numbers too) and simply show ONLY the HIGHLEST value. What do I need to add to this code in order to do this?

strFilter = "[Genre] " & strGenre & " AND [Platform] " & strPlatform

help ASAP appreciated. Project due tommorro!

[DKenny]

The qualifiers are the single quotation marks you have included in the generation of the strPlatform string. Quote marks are not needed when passing a numeric value in a filter. Changing your code to the following should work. I also converted the value of cboPlatform to the "Long" datatype.

Else
        strPlatform = "=" & CLng(Me.cboPlatform.Value)
End If

[barton]

I get the error, 'Type Mismatch' when I try to run the filter under those changes you've advised.


Is there a way to alter the code while keeping the value declared as string??

[DKenny]

What is the datatype of the "Platform" field?

[barton]

The data type is a 'Number'. It refers to a report based on a query.

Here is the code again, please refer to this verison (I made a small mistake in typo :P )

Private Sub cmdapplyfilter_Click()
 
'declare variables in which the values from the combo boxes will be stored in
    Dim strGenre As String
    Dim strPlatform As Long
    Dim strFilter As String
    'if the value in this combo box is null, then show all records
    If IsNull(Me.cboGenre.Value) Then
        strGenre = "Like '*'"
    Else
        strGenre = "='" & Me.cboGenre.Value & "'"
    End If
     'if the value in this combo box is null, then show all records
    If IsNull(Me.cboPlatform.Value) Then
        strPlatform = "Like '*'"
    Else
        strPlatform = "='" & Me.cboPlatform.Value & "'"
    End If
    'combining criteria to form a WHERE clause
strFilter = "[Genre] " & strGenre & " AND [PlatformID] " & strPlatform
    
    With Reports![rptTopRented]
         .Filter = strFilter
         .FilterOn = True
    End With
 
End Sub

I keep getting the same 'Type Mismatch' errors.

[barton]

specifically; cboPlatform refers to a field on a table thats; Data Type: Number Field size: Number

[barton]

Forget it, it worked! THANK YOU!

[DKenny]

Great, don't forget to mark your post as resolved...

[barton]

Just one more thing, what do i insert to display the report so it only shows the MAX value of a field?

What do i need to add to this?

strFilter = "[Genre] " & strGenre & " AND [Platform] " & strPlatform

[DKenny]

You need to change the Query (or SQL statement) that underlies to report to add a Max() to the column that you need to aggregate. All other columns will need to be included in the GROUP BY clause of the query.

[barton]

SO in the query, I would add 'Max()' to the criteria of the field I want to show the max value?


This is what I have tried so far. for field 'A' I used a count function, I save that query. And then Loaded that same query to make another query. I then dragged field 'A' in, and then grouped it by MAX and included other fields like 'Gametitle' yet, it still says the same as it did without the MAX inserted. It just doesn't show the highlest value. Nor does it show the lowest when set on MIN. Do i need to insert it into the criteria instead?

[DKenny]

Can you post the SQL for your Query?
Open the query, then select View\SQL.

[barton]

Can you post the SQL for your Query?
Open the query, then select View\SQL.

SELECT Count(tblRentalHistory.RentID) AS CountOfRentID, tblRentalHistory.GameID, tblRentalHistory.GameTitle, tblRentalHistory.Genre, tblRentalHistory.ESRB, tblRentalHistory.RentalPrice, tblRentalHistory.PlatformID
FROM tblRentalHistory
GROUP BY tblRentalHistory.GameID, tblRentalHistory.GameTitle, tblRentalHistory.Genre, tblRentalHistory.ESRB, tblRentalHistory.RentalPrice, tblRentalHistory.PlatformID;