Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 4th, 2008, 10:36 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default problems with my SQL string

i'm having 2 different problems when i try to string together a SQL statement based on my Text boxes on my form.

The first situation i have is i'm trying to do a look up on our project numbers which unfortunately some of them begin with zero, ie. 06047. and when this is typed in the text box the zero is getting dropped and only the 6047 is being inserted into the string.... is there a way to get around this besides renaming our project numbers?

The second situation i'm running into is i'm trying to string together another criteria for a specific field. i use numbers like 342944 and need to look up all the records that match that field. but when i type this in my text box i use a text validation rule so they appear in the text box as such 34-29-44. now when this is entered the string places the text as 342944 into my query and the query wont run. if i use "342944" then the query runs fine. is there a way to add these Quotations into my string so it inserts the text properly into my query?

examples of my SQL String:

example 1:
strSQL = "SELECT * FROM Q_Records WHERE [ProjectNo] = " & strSearchProjectNo

example 2:
strSQL = "SELECT * FROM Q_Records WHERE [Str1] = " & strSearchStr






  #2 (permalink)  
Old September 5th, 2008, 07:19 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The issue at hand is that your table probably has both these fields set as numeric data types when they should be set as text.

The rule of thumb is that if you're using a field that holds numbers but you will NOT perform mathematical operations, then the field's data type should be set to TEXT, not numeric. An example is ZIP code. Many assume it should be a number, but it should be text. You will never do math on ZIP codes. If you set it as a number, then those in NJ, for example, that start with zero will have that leading zero disappear every time the field is used in a control in a form or report. If you set ZIP code as text, the zero will stay put and the string will always have five to ten characters (ZIP or ZIP+4 with a hyphen).

Set your fields to text. Then the leading zero will always appear.

For the second situation, set it to text, too. What you mentioned with the dashes is not a validation, it's an input mask. For example, a phone number (remember, you don't do math on phone numbers) is set to text. Your input mask for a phone number will probably be !\(999") "000\-0000;;_. This means the table will store ONLY the numbers (to save space), and when you type you only enter the ten digits. The database stores the ten digits only, but you can express how you see them by formatting the textboxes that hold the phone numbers in the form or report. In your queries, when you type the criteria, you type ONLY the digits, no other characters.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #3 (permalink)  
Old September 11th, 2008, 12:20 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i've combed through all the tables and i had everything set to Text so that is why this is still puzzling me as to why its dropping off my zero in front of my job#'s. is there anywhere else i should be looking to correct this or could there be something else happening? it just doesnt make sense. all the properties are set to text and we depend on these job#'s for identifying our company records and if i cant get this to work than i loose the flexibility to search records by job#'s or recording#'s which doesnt seem right to me.... any suggestions???

  #4 (permalink)  
Old September 11th, 2008, 12:23 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Interesting. What about the input masks and formats for those fields? What do they look like?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #5 (permalink)  
Old September 12th, 2008, 09:41 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

both the input mask & format fields are left blank in the table and on the form. the other thing that is strange is i one of the ways i tried to get around this was i created a text box on one of the forms that opens up to equal the text box on the main form (=Forms!F_HomePage!TxtProjectNo)to verify that the value wasn't changing upon opening into another form and the value stayed exactly the same as entered on the main form. so i know that it has to do something with stringing the SQL statement. for whatever reason when i try to use that value in my SQL statement it drops the zero off.... and even if i try to use a series of numbers like for our recording numbers it does not like to string them in correctly as well. for example if use 5039919 as my search criteria it tells me i have a mismatch type but if put them in quotes in my query it runs fine.

  #6 (permalink)  
Old September 16th, 2008, 10:29 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Well, the only other suggestion I can think of is to "force" the project number to be right in your SQL statement by doing this to it (assuming project number is always text with five characters):

example 1:

Dim bytMax as Byte, bytCount as Byte

bytMax = 5

If Len(strSearchProjectNo) < bytMax Then
    For bytCount = Len(strSearchProjectNo) + 1 to bytMax
        strSearchProjectNo = "0" & strSearchProjectNo
    Next bytCount
End If

strSQL = "SELECT * FROM Q_Records WHERE [ProjectNo] = '" & strSearchProjectNo & "'"

As for example 2 in your original post, try:

strSQL = "SELECT * FROM Q_Records WHERE [Str1] = '" & strSearchStr & "'"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #7 (permalink)  
Old September 18th, 2008, 06:29 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i went ahead and changed the data type in my table from text to number to see what would happen and after i did that it worked fine as far as using an actual number. so i opened up the query and entered the number directly into my criteria and when the data field is set to number it runs perfectly and when i change the data field back to text i notice that after i enter my number into the query criteria and hit enter it places quotations around my number. and this is what seems to be my problem with my SQL string cuz when it inserts the number into my criteria it doesnt have quotations around the text that is being inserted. i need to figure out the proper way to write the SQL string to slip in these quotations to run number searchs in a data field that is a text. besides typing them in my text box with the number (cuz that does work!) i know what you are thinking but i cant change my data field to a number cuz our records have both numbers & text so i have keep it this way. any suggestions??

  #8 (permalink)  
Old September 19th, 2008, 07:12 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

I just realized a possible bug in my code. Note that my code did treat the project number as a string to start with. Try:

Dim bytStart as Byte, bytMax as Byte, bytCount as Byte

bytStart = Len(Trim(strSearchProjectNo))
bytMax = 5

If btyStart < bytMax Then
    For bytCount = bytStart + 1 to bytMax
        strSearchProjectNo = "0" & Trim(strSearchProjectNo)
    Next bytCount
End If

strSQL = "SELECT * FROM Q_Records WHERE [ProjectNo] = '" & strSearchProjectNo & "'"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #9 (permalink)  
Old September 19th, 2008, 01:20 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ok i tried your example but still couldn't get it to work and even if i was able to keep the zero in front of my number it would still treat this as a number and not a text. i need to find a way to carry over what ever is entered into the text box and enclose it with quotation marks.... i tried this :

strSearchRecNo = """ strRecNo """

but it inserts it into my query like this = "strRecNo"

is there a way to arrange the characters so that whatever i enter in my text box will be inserted into my query exactly like its entered and surround it with quotation marks???

  #10 (permalink)  
Old September 19th, 2008, 01:50 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ok :) i finally figured out the solution and as i thought it was real simple character placement issue.... here it is:

strSearchRecNo = """" & Me.TxtRecordingNo & """"

by using the quotations in this arrangement no matter what i type in my text box carries over and the quotations are placed onto the text and inserted properly into the query. this is how you can search data text fields with numbers !!! :)

I'd like to thank SerranoG for your time & help !!!



Similar Threads
Thread Thread Starter Forum Replies Last Post
More string problems jroxit Classic ASP Basics 3 November 23rd, 2008 07:24 PM
SQL string in C# welshboy2005 C# 2005 1 June 4th, 2005 12:28 AM
SQL Statement problems tdaustin Classic ASP Basics 1 May 30th, 2005 07:16 PM
Problems with string functions and Access 2000 giggs Access VBA 5 February 25th, 2004 09:43 AM
ASP.NET MSDE Connection string problems Bagheer Classic ASP Databases 0 August 27th, 2003 08:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.