Solved

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

Posted on 2014-01-13
5
393 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 50

Accepted Solution

by:
Gustav Brock earned 500 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 57
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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