Help with a error msg in a Search form

[chungiemo]

Hi Everybody,

I am using Alan Brownes Example of a search form example and I keep getting an error of the following:-

Enter Parameter Value Msg Box with the entered Value "Mar" and I re-enter "Mar" the value is displayed. Then the query works.

I don't know why this msg box keeps on displaying. Hopefully somebody can help me with this.

A bit more info down below.....

Ive created a table that stores family and friends details, and within the search form is a textbox to enter name, and a combo box for Month which has list values from Jan to Dec.

When I pick any month on the combo box and press the filter button it should display the records within the month.

I have a select query that stores the birthday and takes out the month of the birthday which is:-

SELECT tblPerson.ContactID, 
[Firstname]+" "+[Lastname] AS [Full Name], 
Int((Now()-tblPerson!birthdate)/365.25) AS Age, 
IIf(IsNull([Birthdate]),"",Format([Birthdate],"mmm")) AS BMonth, tblPerson.Birthdate, DatePart("m",[BirthDate]) AS Expr1, 
DatePart("d",[Birthdate]) AS Expr2, Format(Date(),"yyyy") AS CurrentYear, 
IIf(IsNull([birthdate]),"",[SplitDOB] & "/" & [CurrentYear]) AS CurrentDOB,
Format([Birthdate],"dd/mm") AS SplitDOB, IIf([CurrentDOB]="","",
DateDiff("d",Date(),[CurrentDOB])) AS DaysToDOB, 
IIf [DaysToDOB]="","DOB Not Given",IIf([DaysToDOB]=0,"Birthday Today",IIf([DaysToDOB]<0,"Birthday has passed","Birthday still to come"))) AS BirthdayStatus 
FROM tblPerson 
WHERE (((tblPerson.Birthdate) Is Not Null))
ORDER BY DatePart("m",[BirthDate]), DatePart("d",[Birthdate]);

and the filter click code as like Allen Brownes

Private Sub cmdFilter_Click()
    
Dim strWhere As String                  
Dim lngLen As Long                      
Const conJetDate = "\#mm\/dd\/yyyy\#       

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterFullName) Then 
        strWhere = strWhere & "([Full Name] Like ""*" & Me.txtFilterFullName  
        & "*"") AND "
End If
    
'Month field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterMonth) Then
        strWhere = strWhere & "([BMonth] = " & Me.cboFilterMonth & ") AND "
End If 
    
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    
        strWhere = Left$(strWhere, lngLen)
            
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Sorry for the long spool, thought I would detail it as much as possible. Any help would be again greatly appreciated.

Man chun

[Stewart Ross]

Hi, and Welcome to Bytes! I have added code tags to your code segments to aid readability, and split up the SQL statement which otherwise appears on one line in the code tagged text.

I guess your problem is at line 15 in the second segment, in the WHERE string. You are referring to your combo box as if it contained a number, but I guess that in fact it contains the short text for the month of March ('Mar'). When translated into the SQL code it is being seen as some form of field name called Mar, hence the parameter request as there is no such field available.

To refer to string literal values in your WHERE clause simply put single quotes before and after:

strWhere = strWhere & "([BMonth] = '" & Me.cboFilterMonth & "') AND "

It is always helpful to test your code and try to work out yourself what is happening. It helps us if we know what you have ruled in or out as a cause, and whether or not you have tested your code line by line (for instance, using the in-built debugging facilities to look at the SQL strings concerned as they are built up). We have a useful introductory article on Debugging in VBA in our HowTo section.

-Stewart

[chungiemo]

That was a real fast reply.

Thanks worked perfect!

Ive another code problem, al post it, any maybe you might spot this one as well.

Thanks again

Man chun