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
2 1589
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: martingerber |
last post by:
Hi,
I have the following script (javascript in html document):
<html>
<head>
<meta http-equiv="content-type"
content="text/html;charset=ISO-8859-1">
<title>Standort Speichern</title>
|
by: Kranman |
last post by:
Hi All,
Love this site, have gotten a lot from it. This is my first time
posting though, so forgive me for any errors.
I have an Access 2000 db where I have a main form of Contractors and on...
|
by: Michael D. Reed |
last post by:
I am using the help class to display a simple help file. I generated the
help file using Word and saving it as a single page Web page (.mht
extension). I show the help file with the following...
|
by: Rahul |
last post by:
Hi Everybody
I have some problem in my script. please help me. This is script file.
I have one *.inq file. I want run this script in XML files. But this
script errors shows . If u want i am...
|
by: this one |
last post by:
I have the following code
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript"...
|
by: casper christensen |
last post by:
Hi
I run a directory, where programs are listed based on the number of
clicks they have recieved. The program with most clicks are placed on
top and so on. Now I would like people to be apple to...
|
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted...
|
by: pic078 via AccessMonster.com |
last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files)
that remains stuck in task manager after exiting the application - you can't
reopen database after exiting as a result...
|
by: oneski |
last post by:
help --- 403 You don't have permission
Im trying to get a basic search to work on my website, but i keep getting a forbidden error come up. Im using WAMP5 server on a vista machine. The error file...
|
by: mattehz |
last post by:
Hey there,
I am trying to upload old source files and came across these errors:
Warning: Invalid argument supplied for foreach() in /home/mattehz/public_html/acssr/trunk/inc_html.php on line 59...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |