Dlookup Problem Text VS Number type

Hello all-
I have a form (in access) that has a section of code which fills in certain 
cells for an excel spread sheet.  Code has worked perfectly for a while, but 
recently I changed the table's 'OrderNumber' field from a Number type to a 
Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it 
works again.  Why does it matter?  I need/would really like it to be a text 
type field.  All help/explinations would help a lot. 

Thanks!
Zach
0
Utf
2/22/2010 7:21:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
1229 Views

Similar Articles

[PageSpeed] 14

Zach -

Did you also update the DLookup statement?   A text field requires the 
single-quote delimiter around it, whereas a number field does not.

-- 
Daryl S


"Zach" wrote:

> Hello all-
> I have a form (in access) that has a section of code which fills in certain 
> cells for an excel spread sheet.  Code has worked perfectly for a while, but 
> recently I changed the table's 'OrderNumber' field from a Number type to a 
> Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it 
> works again.  Why does it matter?  I need/would really like it to be a text 
> type field.  All help/explinations would help a lot. 
> 
> Thanks!
> Zach
0
Utf
2/22/2010 7:56:14 PM
On 22 feb, 20:21, Zach <Z...@discussions.microsoft.com> wrote:
> Hello all-
> I have a form (in access) that has a section of code which fills in certa=
in
> cells for an excel spread sheet. =A0Code has worked perfectly for a while=
, but
> recently I changed the table's 'OrderNumber' field from a Number type to =
a
> Text type. =A0Now my Dlookup doesn't work. =A0I tried changing it back, a=
nd it
> works again. =A0Why does it matter? =A0I need/would really like it to be =
a text
> type field. =A0All help/explinations would help a lot.
>
> Thanks!
> Zach


I suppose the WHERE-part makes the diffrence.

With a text field the Dlookup would look like:
Dlookup("FieldName","TableName","ID=3D'A'")

With a number:
Dlookup("FieldName","TableName","ID=3D1")

Groeten,

Peter
http://access.xps350.com
0
XPS350
2/22/2010 7:59:17 PM
For a number you do something like this
"[PkID] = " & Me.PkID

For a text
"[PkID] = """ & Me.PkID & """"


Why does it matter?
It is crucial to use the correct quotes - delimiters - for numbers, text and 
dates everywhere in an access database in code.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Zach" <Zach@discussions.microsoft.com> wrote in message 
news:36C191DB-35A2-45C7-8E71-E222FB479531@microsoft.com...
> Hello all-
> I have a form (in access) that has a section of code which fills in 
> certain
> cells for an excel spread sheet.  Code has worked perfectly for a while, 
> but
> recently I changed the table's 'OrderNumber' field from a Number type to a
> Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it
> works again.  Why does it matter?  I need/would really like it to be a 
> text
> type field.  All help/explinations would help a lot.
>
> Thanks!
> Zach 


0
Jeanette
2/22/2010 8:01:08 PM
Sorry I forgot to post a snipit of code.  
DLookup("DUEDATE", "Orders", "[ordernumber]=" & [OrderNumber] & " And 
[orderitem] = " & 1)

Daryl....
do you mean like this?
DLookup("DUEDATE", "Orders", '[ordernumber]=' & [OrderNumber] & " And 
[orderitem] = " & 1)

Thanks for the response!

"Daryl S" wrote:

> Zach -
> 
> Did you also update the DLookup statement?   A text field requires the 
> single-quote delimiter around it, whereas a number field does not.
> 
> -- 
> Daryl S
> 
> 
> "Zach" wrote:
> 
> > Hello all-
> > I have a form (in access) that has a section of code which fills in certain 
> > cells for an excel spread sheet.  Code has worked perfectly for a while, but 
> > recently I changed the table's 'OrderNumber' field from a Number type to a 
> > Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it 
> > works again.  Why does it matter?  I need/would really like it to be a text 
> > type field.  All help/explinations would help a lot. 
> > 
> > Thanks!
> > Zach
0
Utf
2/22/2010 8:06:06 PM
Hey Jeanette-
Thank you so much for your reply!  I understand........makes 
since.....already knew that for otherparts of my code.....but obviously 
forgot about the concept as it applied to a Dlookup syntax.  So again, thank 
you for putting a end to my headache!

"Jeanette Cunningham" wrote:

> For a number you do something like this
> "[PkID] = " & Me.PkID
> 
> For a text
> "[PkID] = """ & Me.PkID & """"
> 
> 
> Why does it matter?
> It is crucial to use the correct quotes - delimiters - for numbers, text and 
> dates everywhere in an access database in code.
> 
> 
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
> 
> 
> "Zach" <Zach@discussions.microsoft.com> wrote in message 
> news:36C191DB-35A2-45C7-8E71-E222FB479531@microsoft.com...
> > Hello all-
> > I have a form (in access) that has a section of code which fills in 
> > certain
> > cells for an excel spread sheet.  Code has worked perfectly for a while, 
> > but
> > recently I changed the table's 'OrderNumber' field from a Number type to a
> > Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it
> > works again.  Why does it matter?  I need/would really like it to be a 
> > text
> > type field.  All help/explinations would help a lot.
> >
> > Thanks!
> > Zach 
> 
> 
> .
> 
0
Utf
2/22/2010 8:45:02 PM
Zach -

I think you have it, but just in case, this is it:

DLookup("DUEDATE", "Orders", "[ordernumber]='" & [OrderNumber] & "' And 
[orderitem] = " & 1)

-- 
Daryl S


"Zach" wrote:

> Sorry I forgot to post a snipit of code.  
> DLookup("DUEDATE", "Orders", "[ordernumber]=" & [OrderNumber] & " And 
> [orderitem] = " & 1)
> 
> Daryl....
> do you mean like this?
> DLookup("DUEDATE", "Orders", '[ordernumber]=' & [OrderNumber] & " And 
> [orderitem] = " & 1)
> 
> Thanks for the response!
> 
> "Daryl S" wrote:
> 
> > Zach -
> > 
> > Did you also update the DLookup statement?   A text field requires the 
> > single-quote delimiter around it, whereas a number field does not.
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "Zach" wrote:
> > 
> > > Hello all-
> > > I have a form (in access) that has a section of code which fills in certain 
> > > cells for an excel spread sheet.  Code has worked perfectly for a while, but 
> > > recently I changed the table's 'OrderNumber' field from a Number type to a 
> > > Text type.  Now my Dlookup doesn't work.  I tried changing it back, and it 
> > > works again.  Why does it matter?  I need/would really like it to be a text 
> > > type field.  All help/explinations would help a lot. 
> > > 
> > > Thanks!
> > > Zach
0
Utf
2/23/2010 8:14:02 PM
"Daryl S" <DarylS@discussions.microsoft.com> wrote in message 
news:54A455CD-457E-4143-8201-9D6CB2CF9E30@microsoft.com...
> Zach -
>
> I think you have it, but just in case, this is it:
>
> DLookup("DUEDATE", "Orders", "[ordernumber]='" & [OrderNumber] & "' And
> [orderitem] = " & 1)
>
> -- 
> Daryl S
>
>
> "Zach" wrote:
>
>> Sorry I forgot to post a snipit of code.
>> DLookup("DUEDATE", "Orders", "[ordernumber]=" & [OrderNumber] & " And
>> [orderitem] = " & 1)
>>
>> Daryl....
>> do you mean like this?
>> DLookup("DUEDATE", "Orders", '[ordernumber]=' & [OrderNumber] & " And
>> [orderitem] = " & 1)
>>
>> Thanks for the response!
>>
>> "Daryl S" wrote:
>>
>> > Zach -
>> >
>> > Did you also update the DLookup statement?   A text field requires the
>> > single-quote delimiter around it, whereas a number field does not.
>> >
>> > -- 
>> > Daryl S
>> >
>> >
>> > "Zach" wrote:
>> >
>> > > Hello all-
>> > > I have a form (in access) that has a section of code which fills in 
>> > > certain
>> > > cells for an excel spread sheet.  Code has worked perfectly for a 
>> > > while, but
>> > > recently I changed the table's 'OrderNumber' field from a Number type 
>> > > to a
>> > > Text type.  Now my Dlookup doesn't work.  I tried changing it back, 
>> > > and it
>> > > works again.  Why does it matter?  I need/would really like it to be 
>> > > a text
>> > > type field.  All help/explinations would help a lot.
>> > >
>> > > Thanks!
>> > > Zach 

0
De
3/13/2010 6:03:00 PM
Reply:

Similar Artilces:

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but c...

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

Customize Current View: Filter Problem
Hello, Using Outlook 2002, I have created a category called "Hidden" i outlook. In my calendar, I put all the appointments I don't want t appear in this calendar (i.e. daily reminders). I then filter out all these "Hidden" items by selecting -- View--> Current View--> Customize Current View--> Filter--> More Choices--> Categories--> Hidden (the category I created)--> OK--> SQL--> Edit these criteria directly--> And then I change: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AN ("urn:schemas-microsoft...

Month problem
I have some code where I calculate this month minus 2 month. This goes fine until I get to august. When I use DateSerial to deduct 2 month I get to July, and this makes no sence. I made the following testcode. Sub StrangeMonth() Dim MyDate As Date MyDate = #8/31/2010# MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 1, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 2, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart(&qu...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Rounding Problem
I am using the following formula: ROUND((BH24-BG24)*24,1) Where BH24 = 15:21, BG24 = 15:00 (times based on a 24 hour clock) The answer returned is 0.3, but it should be 0.4. If you round out to 2 places the answer is 0.35. Unless I am missing something 0.35 should round to 0.4. If I use Roundup, it rounds everything up. What am I missing? Hi this is due to Excel's representation of numbers (see: http://www.cpearson.com/excel/rounding.htm) In your case the formula =(BH24-BG24)*24 does not return 0.35 but 0.3499999999999 (Just test it and format the resulting cell with enough decima...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Resolving Email Address to existing contact problem
Has anyone else come across this issue in MSCRM3.0? - when you attempt to resolve an email address to an existing contact in CRM (that does not currently have an email address stored in their details) the contact resolves OK but does not update the email address in the contacts details. Is this by design, or have I found a bug? ...

Offline folder problems #2
Hi Guys, Basically I installed Outlook 97 on my office PC which is connect to microsoft exchange server. For some reason all of my messages were copied into offline folders, this causes a problem, whenever I open up outlook I get this message. Upload of offline changes could not be completed you do not have sufficient permission to perform this operation on this object. See the folder contact or see your system administrator. I do not administrator access on my user profile, every time I open outlook it resets everything I have changed (view etc) and gives that error message. How ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

outlook 2k2 problem
I am using Outlook 2002 with SP3, and i have not had any problems up until a week ago. My password does not seem to save even though i have typed it in correctly in the e-mail acct settings ,and have checked "save password. It saves it as long as i have Outlook open, but as soon as i close it, and then later open it again, my password is gone and Outlook asks me for my e-mail password. So, i re-type and i check "save password". But if i decide to close Outlook (say for the evening, and shut down my computer or something), my password disappears the next time i open Outlo...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Roman and Arabic numbers in SEQ
Running 2003 on XP. Got a doc that has SEQ numbering. The code is: ARTICLE{seq level0\h\r0}{seq level1\h\r0} etc. to level 7 and the last entry is {seq level0\r1\*ROMAN) This displays ARTICLE I The next level down is has this code: {seq level0\c\*ROMAN}.{seq level1\r1 \*arabic} This displays as : I.1, with numbers until article five displaying as V.1, V.2, etc. I want it to display as 1.1, 1.2, etc. I tried replacing the ROMAN with arabic, but it didn't change anything. I have spent hours trying to figure this out. Using reveal formating and styles, they are no styles...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

Spam is a big big problem
Spam is a big big problem ...

Mother of a sumproduct (ish) problem!
At least it is to me - now I humbly beseech your magic :) A1 to A10 contain 10 unique letters, e.g. A,Q,E... B1 to B10 contain 10 unique letters, e.g. Z,B,A... C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q... D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B... E1 to E100 contain values, e.g. 9,1,3,5,7,2... In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for the sample...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

Problem with Database Wizard
I'm trying to generate a diagram based on the contents of an Access database, using the database to provide x,y coords for instances of a Master shape. It seems I need to run the wizard twice, first to link a master, then to generate the drawing. The first bit appears to work OK, but when I do the second bit, Visio says that there is no master in the stencil that it can use. But I know the master is connected, because if I modify the database, then refresh the shapes, they change accordingly. Does anyone have any idea why this isn't working for me ? I'm using Visio 20...