Retrieve SQL Results (1 Viewer)

The_Doc_Man · Feb 21, 2019

The only things that could be wrong with that DLookup are listed below, and I have annotated the cases regarding probability of the problem being in the named facet of this call.

1. [Category] might not be the name of a field - unlikely, based on prior posts
2. [Company Name] might not be the name of a field - unlikely, based on prior posts
3. [Sales Tax Table] might not be the name of a table - unlikely, based on prior posts
4. Me.Name_Choose might not be the name of a control - not so clear, needs verification
5. The bound column for Me.Name_Choose isn't text - need to put a breakpoint so you can examine the value of Me.Name_Choose using the Debug.Print commend in the immediate window.
6. The control Me.Name_Choose is empty - can be verified same way as #5
7. You didn't give DLookup a destination. You didn't show us the code. Or if you did, what I saw wasn't the correct way to call a function.

To use DLookup, the statement usually looks like

Code:

variable = DLookup( "[field]", "[domain]", "criteria clauses" )

You need the brackets when field names contain spaces. Offhand, I can't think of other reasons for a DLookup to fail for your case.

CJ_London · Feb 21, 2019

Following in from docs comments - is the name choose control a list or combo box? If so is the field the row source is based on coming from a field in a table formatted as a lookup? If so then that is most likely your problem

lhooker · Feb 25, 2019

Thanks to everyone ! ! ! I ended up using the following 'DLookup' statement.

Me.Category_Name = DLookup("[Category]", "[Category Table]", "[Company Name] = [Name_Choose] ")

I did run into a problem with values with single quotes in [Company Name] (i.e. Lowe's, Catherine's, Sonic's .etc.). Any suggestions on a solution ?

The_Doc_Man · Feb 25, 2019

Comparing field to field as shown in that DLookup should not care about the values in question because you are not trying to do substitution. What is the aberrant behavior that makes you think you are having a problem?

lhooker · Feb 28, 2019

Doc_Man,

I'm getting the below error . . .

Run-time error '3075'

Syntax error (missing operator) in query expression

For the value of Xyx's

The_Doc_Man · Feb 28, 2019

Though I'm pretty good at what I do, I think I missed the class on "mind reading." For the value of Xyx ??? Where did THAT come into the discussion?

Post SOMETHING for me to go on and I will be happy to consider it.

OK, having made a standard disclaimer, the USUAL cause of that error is that you've got your quoted constant expressions improperly balanced. BUT there is also the possible issue that if you have a text field and are trying to dynamically build an SQL expression via substitution, it is possible that the text field (a) contains a space in its value and (b) was not presented with appropriate quoting. Thus that space makes the expression appear to have TWO text elements separated by nothing. VBA will not bother to verify the two text elements as variables if the syntax says it wouldn't compile anyway.

lhooker · Mar 1, 2019

Doc_Man,

The value Xyx's is an example of the value in the field [Company Name] in the [Category Table] table.

DLookup Statement

Me.Category_Name = DLookup("[Category]", "[Category Table]", "[Company Name] = [Name_Choose] ")

The_Doc_Man · Mar 1, 2019

OK, I didn't at first make the mental leap even though earlier you posted something about Lowe's and Sonic's. Now I see what you meant. Guess I had a white dwarf star moment - became VERY dense.

That kind of behavior says it is trying to do a substitution, but that shouldn't affect the syntax you named. That syntax does NOT involve a substitution. I'm not sure at the moment what is going on.

Code:

1:   Me.Category_Name = DLookup("[Category]", "[Category Table]", "[Company Name] = [Name_Choose] ") 

2:   Me.Category_Name = DLookup("[Category]", "[Category Table]", "[Company Name] = [COLOR="Red"]'[/COLOR]" & [Name_Choose] & "[COLOR="red"]'[/COLOR]")

You showed us syntax like case 1. I would expect an issue for case 2, which DOES involve a substitution and single-quote/apostrophe issues - but you didn't show us that case. I highlighted the apostrophes to make them stand out.

Mark_ · Mar 1, 2019

Something useful when you start having issues with comparisons; replace your CRITERIA with a locally declared variable. Say you'd DIM asWhere as STRING to begin with. Rather than

Code:

Me.Category_Name = DLookup("[Category]", "[Category Table]", "[Company Name] = [Name_Choose] ")

you could use

Code:

asWhere = "[Company Name] = [Name_Choose]"
Me.Category_Name = DLookup("[Category]", "[Category Table]", asWhere)

This would let you debug.print or msgbox asWhere to see exactly what is being passed. For something like this it is fairly useful, but it becomes far more useful when you are either trying to conditionally change your criteria OR where you have rather complex criteria.

lhooker · Mar 13, 2019

Doc_Man/Mark,

I'm back . . . I see the problem. It's the single quote in [Company Name] or [strCompany] (i.e Xyz's) for the DoCmd.RunSQL statement (as shown below).

DoCmd.RunSQL "Insert Into [Sales Tax Table] ([Company Name], Grocery_Amount, Amount, Grocery_Date, Category) Values ('" & [strCompany] & "', '" & [strGroAmount] & "', '" & [strAmount] & "', '" & [strToday_Date] & "', '" & [strCaregory_Name] & "')"

How should the DoCmd.RunSQL statement be coded for single quotes in a field name.

Micron · Mar 13, 2019

you could try
Values ('" & Replace ([strCompany],"'", "") & "', '" & ...
spaced out to read easier
Values ('" & Replace ([strCompany]," ' ", "" ) & " ', ' " & ...

The_Doc_Man · Mar 13, 2019

The problem will be that as long as you have an apostrophe in a data value, you will develop an unbalanced quote sequence and Access WILL barf.

Here are a couple of articles on ways to get around this problem.

https://docs.microsoft.com/en-us/of...include-quotation-marks-in-string-expressions

http://allenbrowne.com/casu-17.html

Read through them and see if you can find a way to build the troublesome expression to avoid use of the apostrophe as a quoting character.

Mark_ · Mar 13, 2019

I'd do it like THIS since Arnel did a wonderful job demonstrating how to use parameters with SQL.

lhooker · Mar 13, 2019

Ok, I tried the solution mentioned in the article. Did I interpret it correctly ? See below for error and code.

strCompany = strQuote & [strCompany] & strQuote

DoCmd.RunSQL "Insert Into [Sales Tax Table] ([Company Name], Grocery_Amount, Amount, Grocery_Date, Category) Values ('" & [strCompany] & "', '" & [strGroAmount] & "', '" & [strAmount] & "', '" & [strToday_Date] & "', '" & [strCaregory_Name] & "')"

Run-time error '3075':

Syntax error (missing operator) in query expression """Xyz's", '99999.99', '9.99', '3/13/13/2019', 'Misc')'.

The_Doc_Man · Mar 13, 2019

Looks like unbalanced quoted double-quotes did you in. You don't need as many brackets as you supplied but you need a LOT more double-quotes. However, you need the special-case doubled quotes only for the field that would have the apostrophe in it. You can use the double-quote-single-quote sequence for the other fields, I believe.

Code:

DoCmd.RunSQL "Insert Into [Sales Tax Table] ( [Company Name], Grocery_Amount, Grocery_Date, Category )  Values ( """ & stgrCompany & """, '" & strGroAmount & "', '" & strAmount & "', '" & strToday_Date & "', '" & strCategory_Name & "' ) ;"

Note also that if your "Grocery_Date" field is of datatype Short Text, you are fine. But if it is a Date field then you ALSO need to qualify that field with the # character. In which case that one portion would resemble

Code:

", #" & strToday_Date & "#, "...

It might at least RESEMBLE what I put up there. To be perfectly honest, I tend to hate quotes in that context as well.

Micron · Mar 13, 2019

single quotes are probably not evident in more than one or two fields over a set of tables, which leads me to wonder what was wrong or unappealing with the Replace function...

The_Doc_Man · Mar 13, 2019

I'm with you, Micron, but the OP has persisted in this line of consideration.

Mark_ · Mar 13, 2019

lhooker,

Did you look at the link I provided? It is to a thread on this forum where Arnel provides a very good solution to your problem. single quotes in entered data are not a problem.

lhooker · Mar 13, 2019

Mark,

If you're referring to 'fnAnySQL() parameter query'. I did, but it's too complicated for me. I'm looking for a simpler solution.

lhooker · Mar 13, 2019

Micron,

How would your solution look with my DoCmd.RunSQL statement ? See statement below . . .

strCompany = strQuote & [strCompany] & strQuote

DoCmd.RunSQL "Insert Into [Sales Tax Table] ([Company Name], Grocery_Amount, Amount, Grocery_Date, Category) Values ('" & [strCompany] & "', '" & [strGroAmount] & "', '" & [strAmount] & "', '" & [strToday_Date] & "', '" & [strCaregory_Name] & "')"

Retrieve SQL Results (1 Viewer)

Immoderate Moderator

Super Moderator

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

Longboard on the internet

Registered User.

AWF VIP

Immoderate Moderator

Longboard on the internet

Registered User.

Immoderate Moderator

AWF VIP

Immoderate Moderator

Longboard on the internet

Registered User.

Registered User.

Similar threads

Users who are viewing this thread