Building a Query by Form Interface

Hello, I'm trying to create a method where a user selects some
criteria in a form and a query then generates the results.  I have
been able to pass numbers successfully in the query but not text.  If
anyone has an easy way of developing this let me know.  The code below
works for numbers but not text Help me please.

Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.*"
strFROM = "UFRRecords s "
If Check2 Then
strWHERE = strWHERE & "  s.[FlagField1] = " & Combo0
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 5)
BuildSQLString = True

End Function

* I believe the issue with text is occuring with "& Combo0 but im
clueless on how to reslove it.

0
takeagoodlook911
6/27/2007 1:02:05 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
805 Views

Similar Articles

[PageSpeed] 21

Text must be enclosed in quotes, so if you have a string variable, you need 
to do the following in all instances:

WHERE Field = '" & stringvariable & "'"

(WHERE Field = singlequote doublequote& stringvariable & doublequote 
singlequote doublequote )
-- 
hth,
SusanV



<takeagoodlook911@yahoo.com> wrote in message 
news:1182949325.892566.168440@c77g2000hse.googlegroups.com...
> Hello, I'm trying to create a method where a user selects some
> criteria in a form and a query then generates the results.  I have
> been able to pass numbers successfully in the query but not text.  If
> anyone has an easy way of developing this let me know.  The code below
> works for numbers but not text Help me please.
>
> Function BuildSQLString(strSQL As String) As Boolean
> Dim strSELECT As String
> Dim strFROM As String
> Dim strWHERE As String
> strSELECT = "s.*"
> strFROM = "UFRRecords s "
> If Check2 Then
> strWHERE = strWHERE & "  s.[FlagField1] = " & Combo0
> End If
>
> strSQL = "SELECT " & strSELECT
> strSQL = strSQL & "FROM " & strFROM
> If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 5)
> BuildSQLString = True
>
> End Function
>
> * I believe the issue with text is occuring with "& Combo0 but im
> clueless on how to reslove it.
> 


0
SusanV
6/27/2007 1:14:24 PM
See:
    Search form - Handle many optional criteria
at:
    http://allenbrowne.com/ser-62.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<takeagoodlook911@yahoo.com> wrote in message
news:1182949325.892566.168440@c77g2000hse.googlegroups.com...
> Hello, I'm trying to create a method where a user selects some
> criteria in a form and a query then generates the results.  I have
> been able to pass numbers successfully in the query but not text.  If
> anyone has an easy way of developing this let me know.  The code below
> works for numbers but not text Help me please.
> 
> Function BuildSQLString(strSQL As String) As Boolean
> Dim strSELECT As String
> Dim strFROM As String
> Dim strWHERE As String
> strSELECT = "s.*"
> strFROM = "UFRRecords s "
> If Check2 Then
> strWHERE = strWHERE & "  s.[FlagField1] = " & Combo0
> End If
> 
> strSQL = "SELECT " & strSELECT
> strSQL = strSQL & "FROM " & strFROM
> If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 5)
> BuildSQLString = True
> 
> End Function
> 
> * I believe the issue with text is occuring with "& Combo0 but im
> clueless on how to reslove it.
0
Allen
6/27/2007 1:19:13 PM
I've got a function I use to wrap text in quotes.  I've seen people use the 
combination of an apostrophe and a quote and multiple quotes and find both of 
these difficult to read in my code.  Because of this, I wrote a simple 
function Quotes( ) that I use whenever I need to wrap text in quotes.  You 
would use this something like:

strSQL = "SELECT * FROM yourTable " _
           & "WHERE [someTextField] = " & quotes(me.txt_box)

Public Function Quotes(ByVal TextToQuote As Variant) As String

    If IsNull(TextToQuote) Then TextToQuote = ""
    
    Quotes = Chr$(34) & TextToQuote & Chr$(34)
    
End Function

HTH
Dale
"takeagoodlook911@yahoo.com" wrote:

> Hello, I'm trying to create a method where a user selects some
> criteria in a form and a query then generates the results.  I have
> been able to pass numbers successfully in the query but not text.  If
> anyone has an easy way of developing this let me know.  The code below
> works for numbers but not text Help me please.
> 
> Function BuildSQLString(strSQL As String) As Boolean
> Dim strSELECT As String
> Dim strFROM As String
> Dim strWHERE As String
> strSELECT = "s.*"
> strFROM = "UFRRecords s "
> If Check2 Then
> strWHERE = strWHERE & "  s.[FlagField1] = " & Combo0
> End If
> 
> strSQL = "SELECT " & strSELECT
> strSQL = strSQL & "FROM " & strFROM
> If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 5)
> BuildSQLString = True
> 
> End Function
> 
> * I believe the issue with text is occuring with "& Combo0 but im
> clueless on how to reslove it.
> 
> 
0
Utf
6/27/2007 1:42:04 PM
Reply:

Similar Artilces:

building an external link based on a cell value
I would like to build an external link in a spreadsheet that automatically changes based on a cell value. For example, I have the following link: =SUMIF('[Location Income Statements 2004.xls]Mar04'! $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'! $18:$18) Instead of Mar04 in the formula above, I would like to use a cell's contents.... cell A8 for example. Is this possible? Thanks, Jason Jason You can use the INDIRECT function. =SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 & "'!$14:$14"),P5... Indirect ...

Limit record count in query
Hello all, I have a query that will return over 65K records (rows). My end goal is to export this to Excel via code. Excel has a limit of 65K rows on a single tab. So, 65K to tab1, the rest of the records to tab2. I figured I would have query1 that would limit the results to 65K, and query2 that would start at 65K +1 to the end. How can I limit query1 to 65K and query2 to start at 65K +1? I'm not sure how efficient this will be but you could try: SELECT TOP 65000 * FROM [query that will return over 65K records] ORDER BY PrimaryKeyField; and SELECT * FROM [query that will retu...

Error on Importing Access Query
I am trying to import Access Query to Excel so I can generate pivot table and chart with live data. However importing external data reads column header only, no values. I have done this before, a while ago. I don't understand why this is not working. Can somebody help me? ...

Keeping Form values after PreCalloutReturnValue.Stop/Abort ?
The system in question has a precallout on the create event, where some data has to be validatet to suceccesfully store them in crm. This is not a problem, but the problem (more an annoyance then a problem) is that if the data was not validated the precallout return abort and the form the user was working with is completely blank. Is there anyway to keep the values the user entered after an abort/stop, so that they didn't have to enter all the values over again, rather then just editing the "wrong values". Cheers... Gaute If you search the Microsoft Dynamics CRM Develop...

Continuous Form & 2 queries
User would like a spread sheet type look (former excel junkie)…but for a good reason. Will describe in nontechnical terms: The data on left half is related to data on right half by a unique common field. But a query that would bring them into a single record source results in the query not being update-able. (The left half data is from a query that has a sum subquery….so I understand why the query is not updateable) So while the left half data is static – the right half data is update-able. Going with a subForm loses the continuous form look they want to scroll thru lots of lines of ...

Automatic resize of controls on form
I'm about to do a new application and so far i have only made none-resizable forms.. I want to try to do an app, that can be resized by the user. I was thinking along the lines of doing a initial size config, based on the screen resolution and then letting the user drag the screen og via the menu select preset resolutions of the app (limited by the gfx capablillities of the system running the app). I'm not familiar with Anchor/Dock as such. I've been playing a little with them, but it seems to me, that they only stretch/schrink the controls in width. I can't s...

Form Size and Scroll Bars
Hello All, We have added a number of fields to the opportunity form. For some reason when the form loads the scroll bar goes all the way to the bottom of the form and the user has to scroll back up to the top of it. Any ideas why this might be happening? Thanks! Hi, Check out JScript in your onload event of opportunity. Is there any code which is moving the focuss to some field at bottom of form? -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. uMar Khan :: Freelance Consultant Email Contact :: imumar at gmail dot com "MDV1457" w...

how to make a query to find the company with large number of selli
Hi I have two tables, one for the companies' info and one for t selling In the selling table there is data for customers, companies, prices etc. What I want to do is making a query to know which company has the large No. of selling. Can any body help me plzzzz What do you mean by "which company has the large No. of selling"? Most sales? Largest dollar amounts? Most items? In other words, which field determines "large"? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ghost" wrote: > Hi &g...

Store.exe memory leak and can not update ESM to latest build
I have win2k sp4 with exchange sp3 build 6249.4. I am trying to install the August rollup update but the build version never changes and my memory leak is still present. What is going on? First of all, it maybe not leak at all. Secondly, tell us how you are installing roll-up. rphometnng wrote: > I have win2k sp4 with exchange sp3 build 6249.4. I am trying to install the > August rollup update but the build version never changes and my memory leak > is still present. > What is going on? Are you expecting to see Exchange 2000 SP4 listed in Exchange System Manager after a...

POP Blank PO Form
I am trying to get 4 decimal places to print on the above form for the Unit price, I can only get it to print 2, does anyone have any suggestions? The items are set for 4 decimal places. thanks -- Doug Doug, The field name for Unit Price is a calcaulted field based on conditions. One way to display four decimals points is to create a new calculated field of currency type and set the format to something like DLR1_4 which prints four decimal places. If you want to include a $ sign using this format then you need to check the box to display it. Check out some of the other formats avai...

Queries 02-22-08
Typically you use a variable for criteria in a query, but can you use a variable for a field? Lets say there are multiple fields in my table, i.e. field1, field2, field3 and so on. At design time I do not know which field to query because the criteria changes every day. One day I might need to query field1, the next day I might need to query field46. Is it possible to use a variable as a field in the SELECT statement? SELECT tblMyTable.Group, MyVariable, tblMyTable.StartDate WHERE tblMyTable.Group = -1; Thanks in advance. No. You cannot use a parameter in a query to determine w...

Form fields vs. document properties
I'm creating common templates for multiple dealers of the same product. For example, one dealer may open my "Client Guide" document and all he needs to do is update approximately 20 fields and he is done. I'm using the standard document properties fields for my company information, but there are 10+ more unique fields I need to do this for. I've been trying all night to use quick parts, building blocks, etc. to accomplish this but I am really struggling. I can use a fill-in field, but it prompts for the field every place I use it. I've enabled th...

reference is not valid message for pivot tables based on Access query
I've looked around but can't find an answer to this. I have a workbook - an xlt - that has 6 worksheets that pull data from different queries in the same Access database. I have several worksheets with on pivot table each. The pivot tables are based on dynamic named ranges in the external data worksheets. I use dynamic ranges because I have some adjacent calculated columns in the worksheets. The workbook has only 6 pivot caches - the same as the number of worksheets that pull data. I copied pivot tables from sheet to sheet so that they'd use the same cache, where approp...

Filter Form by Multi Selection
What I would like is to have the selection fields in the header. Based on the values inputted in the header, for the results to be displayed in the main part of the form. When there is nothing selected in the header, for all the data to show. Also, I would like the result data to be updated on tab. I've got the result data the way I would like it. I just cannot figure out how to filter it in a form. I've seen it done before. Download the sample database here: Search form - Handle many optional criteria at: http://allenbrowne.com/ser-62.html It demonstrates how to ha...

Form Controls in Publisher 2007
I've added some form controls to my web page in Publisher 2007, and can't get them to work. I added a List box, several text boxes, a text area, and reset and submit buttons. When I preview the web page, or when I publish to my web server, and try to use the submit button, I get an error saying the IE can't open the web page. This error makes no sense to me, since the controls are configured to send e-mail to a valid address; it should not be trying to open a web page, it should be trying to send e-mail. I can't find any help anywhere on this. -- Bert Onstott Ber...

'Replace' Query
Evening All, I have, what I hope is an aesy question to answer, regarding the 'replace' function. I have the following formula, (simplified from actual), =SUMIF($H$6:$H$84,$AA$4,I$6:I$84)+($AC$2*$AB$4/2) But I would like to add the following IF function at the start, =IF($X9=0,0, so that the whole thing should read: =IF($X9=0,0,(SUMIF($H$6:$H$84,$AA$4,I$6:I$84)+($AC$2*$AB$4/2)) Firstly, I have tried replacing =SUMIF( with =IF($X9=0,0,SUMIF( but it does not allow this as there is no second closing parenthesis. Also, if this is possble, I would also like it to be able to automatical...

execute FireOnChange() from another Form
can i execute a FireOnChange event on a field from a another form? For example: In the OnSave() event from QuoteProduct form, execute the FireOnChange() event of the totalamount field in the Quote Form Thanks Enrique This is not support "out of box". Try Server SDK, pre/post callouts. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm "Enrique Bravo" wrote: > can i execute a FireOnChange event on a field from a another form? > > For example: > In the OnSave() event from QuoteProduct form, execute the FireOnChange() > event of the totalamoun...

Problem with parameter query using between []...
I am trying to build a parameter query for which I would enter two values and have all the values in between returned. For example, a list of numbers from 1 to 200. On the query I have the following on the Criteria: Between [Enter Product min number] And [Enter product max number] & "*" So when I run the query first window pops up, I enter 100, then second one pops up, I enter 150 and after that I get a message saying that the expression is typed incorrectly or it is to complex. Also, I am trying to get a query of the same kind where I can type on the parameter something like ...

Form-level validation
This past summer we had a computer science student (one semester to go, and a smart guy, so pretty knowledgeable in general) working on various projects. One was a database I had been developing. He provided valuable assistance in quite a number of areas, but in the matter of using validation in the form's Before Update event to verify that required fields contain values, he may have gone a bit code-happy. The form is divided into four tab pages, each of which contains controls pertaining to a different aspect of the information that needs to be recorded (Problem Description, Res...

Message box for form regarding DOB
Hi everyone, I have gotten a bit stuck trying to set up a message box that opens if the form record shows the date of birth as over 2 years old. Ideally when any record is opened and the DOB has been entered the message box should appear warning the user (but not if the text box is empty). There should also be a button to deactivate the warning box for each individual record. The form itself has a 'DOB' text box field but no age specifications. If anyone could help me out I would be really grateful Sam -- Message posted via AccessMonster.com http://www.accessmo...

query help error message
cannot enter value into blank field on 'one' side of outer join "Fipp" <Fipp@discussions.microsoft.com> wrote in message news:B70CF9F6-A79C-436E-80C4-4CF3464528D4@microsoft.com... > What does this mean? What do I need to do? The basic answer is that you have created a Query using two (or more) Tables where one Table cannot be updated. This is most commonly the result of using aggregation (scalar operators, MAX(), MIN(), SUM(), etc.). The specific answer would depend on exactly what your Query is and on your Table schema. Go ahead and post your Query's SQL a...

TCP/IP stack driver (tcpip.sys) interface
Hi! I would like to know if it is possible to create binding between NDIS protocol driver and TCP/IP stack driver (tcpip.sys). Can I make the tcpip driver encapsulate my messages within IP packets, so that I did not have to take care of adding all the other needed headers (IP, ETH)? Thanks for any help! > Can I make the tcpip driver encapsulate my messages within IP packets, = This is not NDIS. This is TDI (pre-Vista) or WSK (Vista+). --=20 Maxim S. Shatskih Windows DDK MVP maxim@storagecraft.com http://www.storagecraft.com On 2 Sep, 19:28, "Maxim S. Sha...

Form Name as Variable
Hi, I have the following subroutine: Public Sub SetMeterBasic(p As Single) ' This subprocedure is used to update the progress meter bar With Forms![FormA] .shpMeterBar.Width = p * .lblMeter.Width .lblMeter.Caption = Format(p, "##%") .Repaint End With End Sub FormA is a form from which I called the main subroutine of the program which then later called this procedure. Is there a way I can pass a form name to Sub SetMeter e.g. so its first line is Public Sub SetMeterBasic(p As Single, FormName as String) where the variable FormName can function as &...

crm form object
Does anyone have, or have a good idea how to get, a reference to the crm form object used with JavaScript? I can figure out crmForm.fieldname.value Ok, but I would also like to know about SubmitCrmForm enums, etc. Thanks, Stephen ...

Addind an external LDAP query into the Exchange 2003 Recipient Lis
Is there a way to take a query from an external LDAP server and place this list of names into a recipient list in my exchange server? I am running Server and Exchange 2003. Currently I am having my users individually setup a LDAP query address list in Outlook. This is becoming a pain as too many users would like to have access to the list now that I have it setup for a few. I have attempted to get the offline GAL from the external server administrators but they are no longer willing to distribute it and are forcing everyone to pull from the LDAP query. I know I can manually create a ...