variable as field name in dlookup help

J

josephff

Registered User.
Local time
Today, 01:53
Joined
Oct 25, 2011
Messages
72
I cant figure this out. im trying to use a variable as my field name lookup in a dlookup statment.

if i change the variable to this it works fine

Code: 
price = "[1 x 1]"

Code: 
    Dim pipesize As String
    Dim price As String
 
    If Me.PipeSizeTxt.Value = 0.5 Then
    pipesize = "1/2"
    ElseIf Me.PipeSizeTxt.Value = 0.75 Then
    pipesize = "3/4"
    ElseIf Me.PipeSizeTxt.Value = 1.25 Then
    pipesize = "1 1/4"
    ElseIf Me.PipeSizeTxt.Value = 1.5 Then
    pipesize = "1 1/2"
    ElseIf Me.PipeSizeTxt.Value = 2.5 Then
    pipesize = "2 1/2"
    ElseIf Me.PipeSizeTxt.Value = 3.5 Then
    pipesize = "3 1/2"
    ElseIf Me.PipeSizeTxt.Value = 4.5 Then
    pipesize = "4 1/2"
    Else
    pipesize = Me.PipeSizeTxt.Value
    End If
 
price = Me.InsulationSizeTxt.Value & "x" & pipesize
 
' just to check that the variable is producing
MsgBox price
 
'is producing the variable and not the lookup    
'Me.TotalPriceTxt.Caption = Nz(DLookup(" '" & [price] & "'", "[Pricing]"))
 
Me.TotalPriceTxt.Caption = Nz(DLookup([price], "[Pricing]"))

can anyone help with this, i think ive been through every combo of quotations and brackets that i can think of, but everytime i get the error.

missing operator in query expression '1x1'.
 
vbaInet said:
Your DLookup syntax is wrong. Have a look at the following links for examples and explanations:

http://access.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php
http://allenbrowne.com/casu-07.html
Click to expand...


how is it wrong, other than possible quotation marks? You arent required to have a where value and as ive stated i can change the variable to

Code: 
price = "[1x1]"
and the dlookup works fine with

Code: 
nz(dlookup([price],"Pricing"))

none of those links show any relation to using a variable as a field name and i have looked up several but to no avail.

It has to be something with the variable declaration. i have tried to add quotation marks to it simliar to this

Code: 
price = " '" & me.insulationsizetxt.value & '"
etc etc

but still cannot get it to work correctly
 
i should mention that in the original post and my original code i was using spaces in the field names. I have removed them in the table and in the code as shown in the second code to try to figure out why i cant get it to work. However, it still will not.
 
I'll explain. Pay attention to the bits marked in red.
josephff said:
'Me.TotalPriceTxt.Caption = Nz(DLookup(" '" & [price] & "'", "[Pricing]"))

Me.TotalPriceTxt.Caption = Nz(DLookup([price], "[Pricing]"))
Click to expand...
Enclosing price in [] inside a DLookup makes the compiler think you are referring to a field name or control name. price in your case is a variable and doesn't require square brackets. Both DLookups have this problem.

The first one contains single quotes which is wrong.

Since you're using the second one, are you saying that there's a field in your Pricing table called 1 x 1?
 
vbaInet said:
I'll explain. Pay attention to the bits marked in red.
Enclosing price in [] inside a DLookup makes the compiler think you are referring to a field name or control name. price in your case is a variable and doesn't require square brackets. Both DLookups have this problem.

The first one contains single quotes which is wrong.

Since you're using the second one, are you saying that there's a field in your Pricing table called 1 x 1?
Click to expand...


Ok that makes sense, i was under the impression that using brackets will contreract against the spaces i have in the variable and thats why they are there. On the other hand, i am referring to a field name.

originally i tried it without them and it still did the same (telling me its missing an operator, which i assume is because its thinking the variable is an expression due to using a "x")

and as i just tried it again without them, it still gives the same error

Code: 
    price = Me.InsulationSizeTxt.Value & "x" & pipesize
    'price = "[1x1]"
    MsgBox price
 
    Me.TotalPriceTxt.Caption = Nz(DLookup(price, "[Pricing]"))

and when i change the dlookup to this

Code: 
Me.TotalPriceTxt.Caption = Nz(DLookup(" '" & price & "'", "[Pricing]"))

it does not error out but produces "1x1" which is the variable instead of the lookup which should be "1.12"
 
in essence, im looking for a field named "1x1" in my table called "pricing" and i want to return the value in the only record in there. I only have one record in there until i get it to work, then i will come back and add my where statements.
 
[] is when a field name or control name has spaces. A variable can not be declared with spaces. Square brackets has nothing to do with the contents of a variable too.

Simply
Code: 
Me.TotalPriceTxt.Caption = Nz(DLookup([COLOR=Blue]price[/COLOR], "[Pricing]"))

Or

Me.TotalPriceTxt.Caption = Nz(DLookup("[" & price & "]", "[Pricing]"))
 
vbaInet said:
[] is when a field name or control name has spaces. A variable can not be declared with spaces. Square brackets has nothing to do with the contents of a variable too.

Simply
Code: 
Me.TotalPriceTxt.Caption = Nz(DLookup([COLOR=blue]price[/COLOR], "[Pricing]"))
 
Or
 
Me.TotalPriceTxt.Caption = Nz(DLookup("[" & price & "]", "[Pricing]"))
Click to expand...

Yeah, so its correct because the variable is a field name as it is in the "field name" place in the dlookup statement (dlookup(fieldname,table,where statement"))

again, i can do the variable directly as

price = "1x1"

and it works fine but as soon as i change it to

price = me.insulationsizetxt.value & "x" & pipesize

it errors out. So it has to be something to do with the control references in the variable declaration. I also tried this and it also did not work

price = " '" & me.insulationsizetxt.value & "' x '" & pipesize & "' "
 
Did you try my second example? It's not the same thing as what you wrote.

Also, do you have a Price field in form which you're running the code from?
 
sorry i didnt see it. I used it and it works, but i still dont understand how. i had this problem on another program and got it to work by mere luck never understanding the reasoning.
 
The "x" in your field name is the cause of all problems. These are the sort of characters you should avoid using when naming anything in Access (or in any other environment).

What I showed you allows the variable to be seen as a variable and return "1x1", then it's enclosed in square brackets and passed to the DLookup() function. Makes sense?
 
yes that makes sense. i know its kind of bad precedure to use something like that in a table field name, but im not sure of any other way to do what im needing to do. I though of maybe using a crosstab query but im not real familiar with how they work.

The situation is this, i need to have a pricing table as such:

sizes as the column names and sizes as the row names, then pick a price based on both like a grid.

so;

..1 2 3 4 5
1 a b c d e
2 b c d e f
3
4
5

and so on...where i say give me the value of 1x2 and it gives me "b".

The only thing i could come up with is to make a table with fields named 1x1, 1x1 1/2, etc etc. This is going to be lengthy and probably use 3 tables and force me to use joins, but im at a loss here.
 
i mean in theory i could make a table like that and say SELECT "1" From "Pricing" WHERE "first column name" = "1/2" or something simliar but i need to be able to store more than one of these "pricing structures" into one table to avoid having to create a new table for each new instance.
 
The problem here is the data structure. You use the sizes as names of separate containers, instead of having a bucket with all sizes thrown in. The size is data too. If your price depends on two values, then you can still have one table for all prices

tblPrices
---------
PriceID
Value1
Value2
Price
 
thanks, i may try to incorporate that, as that seems a little more safe.

SELECT [price] from [tblPrices] WHERE [Value1]= "1" and [Value2]="1"
 
vbaInet said:
Also, you are thinking of things in an Excel way. Access is a relational database, not a flat file or matrix. It will be beneficial if you read up on relational database design:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
http://r937.com/relational.html
Click to expand...


Thanks for the links, i actually own 2 access 2010 books, one for access itself and one for programming access with vba. Understanding relationships are still one of the things i have a hard time grasping. Ill read up on these.

And also, excel is what this pricing structure will be coming from. In essence im trying to incorporate a standard between certain data pieces that will be given and then importing the data into access either manually or programmatically.
 
It won't be easy working with both Excel and Access. These are two programs built for different purposes. It's always easier to export data to Excel but not so easy to import and "re-arrange" data imported from Excel to Access. The reason being, Excel is a flat-file and on the other hand Access is a relational database, so you can see that the challenge is getting your flat file to become relational.

My advise would be to stick with one program.
 
vbaInet said:
It won't be easy working with both Excel and Access. These are two programs built for different purposes. It's always easier to export data to Excel but not so easy to import and "re-arrange" data imported from Excel to Access. The reason being, Excel is a flat-file and on the other hand Access is a relational database, so you can see that the challenge is getting your flat file to become relational.

My advise would be to stick with one program.
Click to expand...


I agree, and mainly i try to stick with access. However, other people in my company do not have access to Access, so they are forced to use excel. I have not dove into importing from excel but i have been able to successfully export (as you mentioned was easier). I would rather not import from excel, but in this pricing table instance its going to be around 436 different prices per customer per jobsite and that equates to a lot of data entry on a form if i have to manualy input from a printed excel spreadsheet.

It may not turn out to be that bad once i try the program for the first time but we'll see. Thanks again for all the information, advice, and links. As always, I come out of all my post with something new learned.
 
You must log in or register to reply here.

Similar threads

M
Solved Duplicate value check - complicated.
Replies
16
Views
1,567
Pat Hartman
P
Garren.Shannon
10 key keypad
Replies
1
Views
573
June7
June7
N
Need help with using Dlookup to find image/photo
Replies
0
Views
446
nax009
N
L
Solved How to include the data, in a specific query field, as part of the file export name
Replies
5
Views
869
Pat Hartman
P
S
Solved Can I use Dlookup/DMIN lookups within VBA where part of the criteria is looking for a 'null' value in a field?
Replies
8
Views
902
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom