473,320 Members | 1,978 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,320 software developers and data experts.

HELP With Where Clause

I have a FIND Form with three unbound combo boxes (the cbos below). I
am able to open frmFindResults, but it is pulling up ALL records, not
those cited in strWhere.

Help!!

Thanks - HERE'S THE CODE:

=====
Private Sub butFind_Click()

'finds based on values in unbound list boxes

On Error GoTo Error_Handler:

Dim strWhere As String

If Not IsNull(Me.cboSSN) Then
strWhere = "dbo_snapshot_aplcnt.aplcnt_ssn = " & Me.cboSSN & " AND
"
End If

If Not IsNull(Me.cboPropAddress) Then
strWhere = strWhere & "dbo_snapshot_aplcnt_addr.addr_line1 = " &
Me.cboPropAddress & " AND "
End If

If Not IsNull(Me.cboDecision) Then
strWhere = strWhere & "dbo_snapshot_decsn_rsn.decsn_rsn_cd = " &
Me.cboDecision & " AND "
End If

strWhere = Left(strWhere, (Len(strWhere) - 5)) 'Removes last 'AND'

'DoCmd.OpenForm "frmFindResults", acFormDS, strWhere

Exit Sub

Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Sub

May 16 '06 #1
3 1354
jv
You seem to be missing a comma before the where parameter. It needs to
be:

DoCmd.OpenForm "frmFindResults", acFormDS,, strWhere

May 16 '06 #2
You also need to put single quotes around your text items, ie.
If Not IsNull(Me.cboSSN) Then
strWhere = "dbo_snapshot_aplcnt.aplcnt_ssn = '" & Me.cboSSN & "' AND "
End If

John

<am*******@verizon.net> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
I have a FIND Form with three unbound combo boxes (the cbos below). I
am able to open frmFindResults, but it is pulling up ALL records, not
those cited in strWhere.

Help!!

Thanks - HERE'S THE CODE:

=====
Private Sub butFind_Click()

'finds based on values in unbound list boxes

On Error GoTo Error_Handler:

Dim strWhere As String

If Not IsNull(Me.cboSSN) Then
strWhere = "dbo_snapshot_aplcnt.aplcnt_ssn = " & Me.cboSSN & " AND
"
End If

If Not IsNull(Me.cboPropAddress) Then
strWhere = strWhere & "dbo_snapshot_aplcnt_addr.addr_line1 = " &
Me.cboPropAddress & " AND "
End If

If Not IsNull(Me.cboDecision) Then
strWhere = strWhere & "dbo_snapshot_decsn_rsn.decsn_rsn_cd = " &
Me.cboDecision & " AND "
End If

strWhere = Left(strWhere, (Len(strWhere) - 5)) 'Removes last 'AND'

'DoCmd.OpenForm "frmFindResults", acFormDS, strWhere

Exit Sub

Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Sub

May 17 '06 #3
Thank you both very much! I incorporated both of your suggestions and
the Find is now working.

===

May 17 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robby McGehee | last post by:
I need this to work: SELECT FROM WITH (NOLOCK) where ='a' GROUP BY , HAVING COUNT () > 1 The problem is that I get an error that needs to be in the GROUP BY clause or aggregate function. if...
3
by: A.V.C. | last post by:
Hello, I found members of this group very helpful for my last queries. Have one problem with CASE. I can use the column name alias in Order By Clause but unable to use it in WHERE CLAUSE. PLS...
4
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the...
17
by: the other john | last post by:
This should be fairly basic but I can't think of how to do this and I'm running out of time! I am developing a picture gallery and I can't figure out how to select "one" picture from each...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
6
by: Jeff Kowalczyk | last post by:
I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid,...
8
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
9
by: Philip Pinkerton | last post by:
When trying to connect to database via the pgAdmin3 GUI it asks for a password. I use the same passworrd as I did when I connect to the DB via command line but I get Ident error? how do I set,...
8
by: Jerry | last post by:
I am a MySQL and PHP newbie. I am having trouble getting the $w variable in my code below passed to mysql. When I use the value of $w directly in the Where clause, the correct rows are returned....
2
by: Jim.Mueksch | last post by:
I am having a problem with using calculated values in a WHERE clause. My query is below. DB2 gives me this error message: Error: SQL0206N "APPRAISAL_LESS_PRICE" is not valid in the context where...
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...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...
1
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...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.