473,289 Members | 1,923 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Help with a error msg in a Search form

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:-

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPerson.ContactID, 
  2. [Firstname]+" "+[Lastname] AS [Full Name], 
  3. Int((Now()-tblPerson!birthdate)/365.25) AS Age, 
  4. IIf(IsNull([Birthdate]),"",Format([Birthdate],"mmm")) AS BMonth, tblPerson.Birthdate, DatePart("m",[BirthDate]) AS Expr1, 
  5. DatePart("d",[Birthdate]) AS Expr2, Format(Date(),"yyyy") AS CurrentYear, 
  6. IIf(IsNull([birthdate]),"",[SplitDOB] & "/" & [CurrentYear]) AS CurrentDOB,
  7. Format([Birthdate],"dd/mm") AS SplitDOB, IIf([CurrentDOB]="","",
  8. DateDiff("d",Date(),[CurrentDOB])) AS DaysToDOB, 
  9. IIf [DaysToDOB]="","DOB Not Given",IIf([DaysToDOB]=0,"Birthday Today",IIf([DaysToDOB]<0,"Birthday has passed","Birthday still to come"))) AS BirthdayStatus 
  10. FROM tblPerson 
  11. WHERE (((tblPerson.Birthdate) Is Not Null))
  12. ORDER BY DatePart("m",[BirthDate]), DatePart("d",[Birthdate]);
and the filter click code as like Allen Brownes

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.  
  3. Dim strWhere As String                  
  4. Dim lngLen As Long                      
  5. Const conJetDate = "\#mm\/dd\/yyyy\#       
  6.  
  7. 'Another text field example. Use Like to find anywhere in the field.
  8. If Not IsNull(Me.txtFilterFullName) Then 
  9.         strWhere = strWhere & "([Full Name] Like ""*" & Me.txtFilterFullName  
  10.         & "*"") AND "
  11. End If
  12.  
  13. 'Month field example. Do not add the extra quotes.
  14. If Not IsNull(Me.cboFilterMonth) Then
  15.         strWhere = strWhere & "([BMonth] = " & Me.cboFilterMonth & ") AND "
  16. End If 
  17.  
  18. '***********************************************************************
  19. 'Chop off the trailing " AND ", and use the string as the form's Filter.
  20. '***********************************************************************
  21. 'See if the string has more than 5 characters (a trailng " AND ") to remove.
  22.     lngLen = Len(strWhere) - 5
  23.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  24.         MsgBox "No criteria", vbInformation, "Nothing to do."
  25.     Else                    
  26.         strWhere = Left$(strWhere, lngLen)
  27.  
  28.         'Finally, apply the string as the form's Filter.
  29.         Me.Filter = strWhere
  30.         Me.FilterOn = True
  31.     End If
  32. 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
Oct 15 '08 #1
2 1588
Stewart Ross
2,545 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 15 '08 #2
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
Oct 15 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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>
3
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...
1
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...
1
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...
2
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"...
23
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...
0
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...
9
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...
1
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...
4
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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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 using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.