dlookup number stored as text

[JamesN]

Hi,

I am using a dlookup to fill a form in based on the CaseID field selected. The CaseID is currently stored as a number and works fine. However, I now require the dlookup to work when the CaseID is a number stored as a text, when i change the tabe field to text it breaks. Is it possible for the dlookup to find the number stored as text? Getting #error! currently

=DLookUp("ExceptionQueryInvestigatedThoroughly","qadata","CaseID = " & [Forms]![EvaluationForm]![caseid])

Thanks

 

[JHB]

The below should do it.

=DLookUp("ExceptionQueryInvestigatedThoroughly","q adata","CaseID = '" & [Forms]![EvaluationForm]![caseid]) & "'"

 

[Isskint]

hi James

I suppose the first question is how is a numeric field storing non numeric values?

That aside, so long as the CaseID does not contain non numeric characters (eg its a number but formatted as text), i would think the following using Val() should work;

DLookUp("ExceptionQueryInvestigatedThoroughly","q adata","CaseID = " & Val([Forms]![EvaluationForm]![caseid]))

Edit:
Or if there are non numeric characters (!!!!:banghead then do as JHB has suggested

 

[gemma-the-husky]

you do need to understand the difference between numbers and text.

generally you will be OK, although if you try to join a number to a text, you will get a type mismatch.

I would have thought a "case number" is actually TEXT, not a number.

ie - you won't want to add case numbers together. However, if you want to "increment" a text value to get the "next" case number, it's a bit more difficult.

Where you need to take care, is if your case numbers have leading zeros, as a "number" can't distinguish between say, "0123" and "00123".

 

[JamesN]

Hi both,

I've tried the suggested methods but still receiving a #error!

Unsure if i have explained properly....

The CaseIDs stored are fully numeric values eg 123456789. I've stored them as text as when publishing the database to sharepoint when formatted as number values (general format) they are being automatically changed to standard format with commas etc and i don't want that.

Any suggestions appreciated

Thanks

 

[JHB]

..when i change the tabe field to text it breaks.

..
The CaseIDs stored are fully numeric values eg 123456789.

Two contradictory explanations so ...

 

[JamesN]

Apologies been messing around with this issue all morning !!

The dlookup works fine when the CaseID field in the table is formatted as a number.

However, when changing the format of the CaseID field to text it breaks the formula and unsure how to tweak it to get it to work.

All of the values stored in the CaseID field are all numeric values

 

[JHB]

..
However, when changing the format of the CaseID field to text it breaks the formula and unsure how to tweak it to get it to work.

If the field type is TEXT, my solution should work. But is the table name = "q adata" correct?

 

[JamesN]

The field data type is text, i changed this in design view. However the values within the field are all numeric. Is it possible to run a dlookup with this setup?

The table is 'qadata', i removed the space when entering the formula

 

[Minty]

The field data type is text, i changed this in design view. However the values within the field are all numeric. Is it possible to run a dlookup with this setup?

The table is 'qadata', i removed the space when entering the formula

You are confusing yourself. If the field type is text your values are text as well. The text values just happen to all be numbers.

You are dealing with text so need quotes in the criteria.

 

[JamesN]

Thanks for clearing that up, still new to access so taking a while to click.

Using the below formula since it is a text but still getting #error! Unable to see any issues with it, all tables and combos match....

=DLookUp("Department","qadata","CaseID = '" & [Forms]![EvaluationForm]![caseid]) & "'"

 

[Galaxiom]

=DLookUp("Department","qadata","CaseID = '" & [Forms]![EvaluationForm]![caseid]) & "'"

Closing parenthesis in the wrong place. It is part of the function, not the parameter.

=DLookUp("Department","qadata","CaseID = '" & [Forms]![EvaluationForm]![caseid] & "'")

 

[JamesN]

That has worked. Thanks all

 

[missinglinq]

The CaseIDs stored are fully numeric values eg 123456789. I've stored them as text

Much of the confusion, here, is caused by the above statement, repeated in one form or another. Your CaseIDs are not 'fully numeric values' if they're stored as Text! They are simply all digits...and should be referred to as that!

Linq ;0)>

 

[gemma-the-husky]

for what it's worth, I find it MUCH simpler to use chr(34) rather than nested " characters.

so


=DLookUp("Department","qadata","CaseID = " & chr(34) & [Forms]![EvaluationForm]![caseid] & chr(34) )