Solved

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

Posted on 2014-01-13
5
381 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
  • 3
5 Comments
 
LVL 49

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now