?
Solved

Access - Currentdb "INSERT INTO WHERE"  when a where item is Blank string

Posted on 2014-01-13
5
Medium Priority
?
407 Views
Last Modified: 2014-01-13
Hello all,

I have a Currentdb.Execute INSERT INTO problem.

In the code I'm using DAO.RecordSet to select a record that I want certain info from that DAO selection to be inserted into another table.

I'm using a strSQL string to set the SELECT of the DAO dynaset.  However, all of the fields are name fields which some of the fields are BLANK.

So my "WHERE clause is bombing out.  I remember sometime in the past an Expert commenting that if my string could be blank I should us Variant instead of String.  SO, I set Dim of vaiables to variant (maybe this is where I'm messing up)

Anyway My dims and section of the code are:
Dim strSQL4 As String
Dim rs4 As DAO.Recordset
Dim LResponse As Integer, LResponse2 As Integer, LResponse3 As Integer
Dim strLName As Variant, strFName As Variant, strMName As Variant, strSufName As Variant, strEntityName As Variant

....
.....
.....
strLName = rs3!RecLName
strFName = rs3!RecFName
strMName = rs3!RecMName
strSufName = rs3!RecSufName
strEntityName = rs3!RecEntityName

strSQL4 = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName FROM tbl_Parties WHERE RecLName='" & strLName & "' AND RecFName='" & strFName & "' AND RecMName='" & strMName & "' AND RecSufName='" & strSufName & "' AND RecEntityName='" & strEntityName & "')"

Set rs4 = CurrentDb.OpenRecordset(strSQL4, dbOpenDynaset)

Open in new window


I have a Set rs3 also where the variables are coming from......
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39775915
The SQL4 string is correct so there must be another reason for the "bombing out".

Try inserting at line 16: Debug.Print strSQL4

What error do you see?

/gustav
0
 

Author Comment

by:wlwebb
ID: 39775925
This is what was in the Immediate window:

SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName FROM tbl_Parties WHERE RecLName='Lowers' AND RecFName='Rensy' AND RecMName='W' AND RecSufName='' AND RecEntityName='')

Note: The RecSufName and RecEntityName are both blank in the tbl_Parties.
0
 

Author Comment

by:wlwebb
ID: 39775934
DUH...

Never mind.........  shouldn't be a closing bracket ")"
0
 

Author Closing Comment

by:wlwebb
ID: 39775938
Thanks for the help gustav.......   Your comment helped me see the err of my ways.
0
 
LVL 58
ID: 39776374
<< I remember sometime in the past an Expert commenting that if my string could be blank I should us Variant instead of String.>>

  Just a note on this; you need to use a variant if a value can be NULL.   That's not the same thing as a blank string, which a string variable can handle:

 Dim strMyString as string

 strMyString = ''

but this won't work:

 strMyString = Null

  Null is used to indicate the absense of a value.   A blank string or a zero (for numerics) might be a valid value, which is why we have a Null indicator.

 For example, in the case of a middle name, say your supposed to ask each person for their middle name when they register.   First person you forget to ask, so the value would be Null because you have no idea what it is.

 Second person you ask says "I don't have one".  That would be a zero length (or blank) string.    You know that they don't have one, not that you don't know what it is.

 For numerics, it might be a bit clearer; if your recording temps, a zero is a valid temperature.

HTH,
Jim.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question