Solved

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
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 …

695 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