Rlane313
Posts: 7
|
| Posted: 01/15/2009, 6:29 AM |
|
Hello,
I am having an interesting problem with one of my forms. I am hoping someone could enlighten me to a resolution.
I am using CodeCharge Studio version 2.3.2.28 and publishing in asp on a local Windows 2003 intranet server, connected to an Access 2000 database.
This form is a year-end inventory tag capture system I custom built for a client. It works perfect in almost all aspects but a few. Basically the physical inventory is done and written on a special inventory tag, then office workers enter the data on that tag into the system to reconcile on hand reported by the accounting system. When the office worker goes to the form, they enter several required fields and some code I have in it using the CCDLookup function looks up the item number entered and retrieves the description to automatically enter in the description field of the tag entry form and for the office worker to compare what is written on the tag to what is in the accounting system to verify that it is the correct item. This all functions perfectly until there is an item number that has a dash in it. The CCDLookup function seems to treat this as a subtraction and it successfully looks up the description of an item with after the math is done.
Example:
Item# 410342-3 Correct description = Apple
Item# 410339 Correct description = Orange
The CCDLookup function selects Orange as the description when item# 410342-3 is entered.
Here is the code that is run upon verification:
tbl_InventoryTags.ITEMDESC.Value = CCDLookup("ITEMDESC", "QRY_LU_GP_Inventory_Items_All", "ITEMNMBR LIKE " & tbl_InventoryTags.ITEMNMBR.Value , DBintranet2)
The ITEMNMBR field in the database and in the form is a text type field.
If I add single quotes around what is typed in the Item# field in the form the CCDLookup function finds the correct description.
I tried adding the single quotes withing double quotes in the example below and it helped with items that have the dash in it, but then had problems finding items that have other characters in it such as item number U620121. That just comes up with a syntax error.
tbl_InventoryTags.ITEMDESC.Value = CCDLookup("ITEMDESC", "QRY_LU_GP_Inventory_Items_All", "ITEMNMBR LIKE " & "'" & tbl_InventoryTags.ITEMNMBR.Value & "'", DBintranet2)
Can someone let me know what should be altered in the code to facilitate getting the CCDLookup function to match exactly what is entered in the field?
Randy
|
 |
 |
micjon
Posts: 8
|
| Posted: 01/15/2009, 6:43 AM |
|
I believe this works in version too, but try using the ToSQL function on your DB Object to specify that it is text not integer like this:
CCDLookup("ITEMDESC", "QRY_LU_GP_Inventory_Items_All", "ITEMNMBR = " & "'" & DBintranet2.ToSQL( tbl_InventoryTags.ITEMNMBR.Value, ccsText) & "'", DBintranet2)
This should tell the DB Engine to use the value as a string....
|
|
|
|
Rlane313
Posts: 7
|
| Posted: 01/15/2009, 12:27 PM |
|
Thank you micjon!!! That worked very well.
Although in my system the double quote then single quote then double quote didn't work. I just removed them and all was well.
Great job!!
|
|
|
|
Oper
Posts: 1195
|
| Posted: 01/15/2009, 1:58 PM |
|
Quote Rlane313:
Thank you micjon!!! That worked very well.
Although in my system the double quote then single quote then double quote didn't work. I just removed them and all was well.
Great job!!
Yes you are right,
DBintranet2.ToSQL( tbl_InventoryTags.ITEMNMBR.Value, ccsText) will add the single QUote itself
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
micjon
Posts: 8
|
| Posted: 01/19/2009, 6:56 AM |
|
I'm glad it worked for you and yeah, sorry about that - it's been a while since I've actually used that function - I forgot that it automatically adds any needed quotes for you.
Glad I could help!
Have a good week and happy coding!
|
|
|
|
|
