Beginner Needs Help with Access 2K Search

molsonbubba

New member
Local time
Today, 03:36
Joined
Apr 20, 2005
Messages
8
Hello,

I am writing a serach page in ASP for Access 2000. Search is for a booking number previously entered by a customer. The booking number is in textnumber fromat - BLAH1234. The BookingNumber field in Aceess is set to Text.

If I canhge field to Number and drop BLAH part then search sees 1234. I relzie I can drop the BLAH part and the problem is gone but I need the BLAH part.

Is it possible to convert BLAH1234 into a text or number string only?

Thanks.
 
Last edited:
Alex;

I wouldn't normally mention spelling or grammar but in this case I think that much of the meaning of your question has been lost because of it, so please forgive me if I misunderstood the problem.

You say that the BookingNumber is in text format, then ask if it is possible to convert BLAH1234 (your booking number) into text ... but you've already got it in text format; as far as I know the only way to store the value BLAH1234 is as text (or memo which is just a form of text) because it contains at least one non-numeric value. Consequently it is not possible to store the value as number only.

I'm going to hazard a guess here and presume that what you would like to do is to be able to search on part of a booking number, rather than the whole number. This can be achieved by using a wildcard "*" in the search criteria; the asterisk tells the query to allow any number of characters of any value to be present in the field.

First, let's assume that the text to be searched for is in a textbox called txtSearchValue on your form. For the BookingNumber field in your query use the following for the criteria:

LIKE "*" & [Forms]![frmFormName]![txtSearchValue] & "*"

Where you replace frmFormName with the name of your form.

The above criteria will return all bookings if the search value text box is left blank. If you only enter one character, say "2", into the search value text box then all bookings with a 2 in, no matter where in the booking number it appears, will be returned.

I will, however, caution you about using a search like this by itself. You will need to ensure that a customer only has access to the their own bookings and to do this you will need to either validate the customer prior to carrying out the search (in which case you could just list all of their bookings) or to require additional customer validation information in the query. Of course if this particular query is for use only by a system operator then you might want to have full search functionality (but will still need to validate the customer prior to giving out or changing information).

Hope this helps.

Tim

PS: An easy way to build the criteria for a query is to click on the Build icon (a wand with three stars and elipises) on the Query Design toolbar. This will then allow you to select the form and controls on the form, thus ensuring that the correct format is used to specify the criteria.
 
Hello Tim,

Thank you. You are right, I should have checked the spelling. Quite embarrassing.

Down to business. Thank you very much for a very detailed and clear response.

You must have guessed that I am not a pro when it comes to ASP programming. I did not know why Access was preventing my search script from seeing BLAH1234. I assumed it was because it contained text and numeric values being entered together into a text formatted column. So I wanted to know if there was a way to make Access see BLAH1234 as a text value only, not text and numeric value. To do so I assumed we would need to convert BLAH1234 into a text value only. Your explanation was great.

However I am still confused why search is working for 1234 when stored as numeric value or BLAH when stored as text value. Why does it not work for BLAH1234? Is the problem with the value being text and numeric combined, or I need to adjust the code?

The wild card suggestion would not work for me as I needed to build a search script that will pick up only one correctly entered booking number at time, and only when it is entered in its entirety when searched for. For example, if your booking # is BLAH1234, then search would only return the information if you enter BLAH1234, not 23 or BL or BLAH123.

Regards,
Alex
 
Alex,

If you want to get an exact match then replace the Like "*" & with an equals sign and remove the trailing "*" thus,

=[Forms]![frmFormName]![txtSearchValue]

molsonbubba said:
However I am still confused why search is working for 1234 when stored as numeric value or BLAH when stored as text value. Why does it not work for BLAH1234? Is the problem with the value being text and numeric combined, or I need to adjust the code?

Is your booking number stored in a single text field? Is the search differentiating between upper and lower cases? Have you tried the code suggested above in your query?

Going back to your first post: You should carry out error checking on the search term once the user has entered it (and before you use it in a search). If it is too long or too short then warn them immediately and go back to the entry routine. You could also carry out checks on the search string to ensure that it is in the correct format (although this will require more code). Don't forget that you can use input masks in text boxes to force the correct format of entered text; check out Access help for more detail on that (press F1 when in the Input Mask properties field).

Tim
 
Posting your search code would help ppl to spot the problem for you :)

Peter
 

Users who are viewing this thread

Back
Top Bottom