Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-13
5
Medium Priority
?
413 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 52

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

618 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