Query Criteria from TextBox : Microsoft, Access, 2003, Query Criteria from Userform

May 17, 2008 05:14am pdt

1. capricorn1 1,301,290
2. DatabaseMX 890,113
3. LSMConsu... 482,407
4. boag2000 335,153
5. harfang 313,826
6. peter57r 272,376
7. TextReport 229,085
8. GRayL 209,614
9. ee_rlee 170,073
10. cactus_data 154,536
11. jimhorn 151,903
12. matthews... 147,637
13. JezWalters 137,435
14. jefftwilley 126,171
15. puppydog... 102,935

1. capricorn1 8,958,328
2. nico5038 5,685,831
3. rockiroads 5,398,237
4. peter57r 3,716,871
5. LPurvis 3,361,835
6. shanesue... 3,288,317
7. LSMConsu... 3,217,997
8. harfang 2,930,659
9. stevbe 2,838,604
10. DatabaseMX 2,773,449
11. jimhorn 2,571,495
12. GRayL 2,369,458
13. flavo 2,104,083
14. mbizup 1,976,668
15. jadedata 1,963,977

02.19.2008 at 03:18AM PST, ID: 23173967

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

6.2

Query Criteria from TextBox

Zone: Microsoft Access Database

Tags: Microsoft, Access, 2003, Query Criteria from Userform

Hi Experts I have a select query that uses text boxes from a form as its criteria. The SQL looks as follows:
SELECT GLExtract.BusinessGroup, GLcodes.RCode, GLExtract.DataItem, GLExtract.Month, GLExtract.period
FROM GLcodes INNER JOIN GLExtract ON GLcodes.GLaccount = GLExtract.DataItem
GROUP BY GLExtract.BusinessGroup, GLcodes.RCode, GLExtract.DataItem, GLExtract.Month, GLExtract.period
HAVING (((GLExtract.BusinessGroup)=[Forms]![QryMonthRpt]![BGINtxt]) AND ((GLcodes.RCode)=[Forms]![QryMonthRpt]![INPUTtxt])
ORDER BY GLExtract.DataItem;

I need to incorporate the following and into the query: AND ((GLExtract.DataItem)=[Forms]![QryMonthRpt]![GLcritTXT])) (it will just need to be added to the "HAVING" statement.
Although the value in the text box (when I stick it in the criteria field of the query) works perfectly well in the actual query, it seems that referencing the TextBox ([GLcritTXT]) doesn't work, any ideas what I could try to feed the TextBox value into the Query....

I'm sure it should be something simple but can't get it to work.
Any help appreciated.
Martywal

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: martywal

Solution Provided By: bluelizard

Participating Experts: 1

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

02.19.2008 at 03:45AM PST, ID: 20927354

Rank: Guru

bluelizard:

before you run the query, you can store the text box's value in a global variable (e.g. declared with "Public" in a separate module) and then use that variable in the query.

--bluelizard

02.19.2008 at 03:47AM PST, ID: 20927365

martywal:

And refer to the module in the Query?
Not sure what you mean.

02.19.2008 at 03:49AM PST, ID: 20927376

Rank: Guru

bluelizard:

or, nicer way: you create a new module with a normal (private) variable and two public functions (see code below).

then, in your form, before running the query, set the value by calling set_s(me.[GLcritTXT]). in your query, use get_s() to use the value.

--bluelizard

                       
                                dim s as string
 
public sub set_s(byval i as string)
  s = i
end sub
 
public function get_s() as string
  get_s = s
end function

Open in New Window

02.19.2008 at 03:55AM PST, ID: 20927412

martywal:

Not sure if we are talking about the same thing.
I don't think you can call a module when you run a query.
The TextBox [GLcritTXT] has the correct value.
If I copy that value and paste it into the query (in field DataItem) it seems to work fine.
I know that if I made a table by pulling the value from the text box into the SQL statement it would work but am trying to avoid building it that way.

02.19.2008 at 04:13AM PST, ID: 20927506

Rank: Guru

bluelizard:

no, no, you don't "call the module" (that's not a macro), what you do is you call a function (which is, incidently, defined in a module) from within your query (that's possible). let me give you a simple example to show you how this works:

suppose we have a module (its name doesn't matter) that holds this code:

public function get_s() as string
get_s = "abcd"
end function

because this function is "public", in your whole access application (including in the queries), you can "say" get_s(), and this will give you "abcd". so, you could write a query like this:

SELECT * FROM mytable WHERE name=get_s()

and this would give you all records where name is "abcd".

see what i mean?

so, all you need now is to extend the module so that the function doesn't always [stupidly] return "abcd", but that your form can set the value dynamically (before it calls the query), and this is what the code i posted above does.

--bluelizard

02.19.2008 at 04:15AM PST, ID: 20927524

Rank: Guru

bluelizard:

if you want step-by-step instructions, please post here how your query is called. if it's called when the user clicks a button on the form, please post the button's code. if it's run in case of an event, please post the event code.

--bluelizard

02.19.2008 at 04:33AM PST, ID: 20927623

martywal:

Hi again bluelizard, thanks for your help
I have put the public function in as you've indicated above and put in the criteria field of the query
"name"=get_s()
it doesn't seem to like this....

02.19.2008 at 04:53AM PST, ID: 20927752

Rank: Guru

bluelizard:

>it doesn't seem to like this....
what do you mean? -> do you get errors, or just no records? are you using the regular "query design view" (with tables shown in the top half, and the fields listed in excel-like fashion in the lower half), or are you entering the query directly as SQL code?

BTW: your query would then have to be written as follows:

SELECT GLExtract.BusinessGroup, GLcodes.RCode, GLExtract.DataItem, GLExtract.Month, GLExtract.period
FROM GLcodes INNER JOIN GLExtract ON GLcodes.GLaccount = GLExtract.DataItem
GROUP BY GLExtract.BusinessGroup, GLcodes.RCode, GLExtract.DataItem, GLExtract.Month, GLExtract.period
HAVING (((GLExtract.BusinessGroup)=get_s()) AND ((GLcodes.RCode)=[Forms]![QryMonthRpt]![INPUTtxt])
ORDER BY GLExtract.DataItem;

--bluelizard

02.19.2008 at 04:58AM PST, ID: 20927787

martywal:

OK Clearly getting there I'm getting the following error
"Invalid use of Me keyword"
Thanks again

02.19.2008 at 05:04AM PST, ID: 20927840

Rank: Guru

bluelizard:

this happens when you want to *set* the value, right? "me...." only works if a code is executed in the form itself (for example, as a code "behind" a button on the form). where is your code that sets the value (i.e., calls set_s)?

--bluelizard

02.19.2008 at 05:59AM PST, ID: 20928284

martywal:

OK I have this in the Public Module:
Dim s As String

Public Sub set_s(ByVal i As String)
s = i
End Sub

Public Function get_s() As String
get_s = s
End Function

The query looks like this:
SELECT GLExtract.BusinessGroup, GLcodes.RCode, GLExtract.DataItem, GLExtract.Month, GLExtract.period
FROM GLcodes INNER JOIN GLExtract ON GLcodes.GLaccount = GLExtract.DataItem
GROUP BY GLExtract.BusinessGroup, GLcodes.RCode, GLExtract.DataItem, GLExtract.Month, GLExtract.period
HAVING (((GLExtract.BusinessGroup)=[Forms]![QryMonthRpt]![BGINtxt]) AND ((GLcodes.RCode)=[Forms]![QryMonthRpt]![INPUTtxt]) AND ((GLExtract.DataItem)=set_s([me].[GLcritTXT])))
ORDER BY GLExtract.DataItem;

I am now getting the message "Undefined Function 'set_s' in expression
Your help is appreciated

02.19.2008 at 06:12AM PST, ID: 20928380

Rank: Guru

bluelizard:

two things:

1) to use the value in your query, use get_s(), not set_s(....) (and don't put any parameters in the parentheses)
2) BEFORE you run the query, you must make sure that this line of code runs:
set_s([me].[GLcritTXT])
(this will set the value that the query will use afterwards)

--bluelizard

02.19.2008 at 06:16AM PST, ID: 20928415

martywal:

When I set it up that way I get:
"External name not defined"
It is looking at the [Me].

02.19.2008 at 07:23AM PST, ID: 20929172

Rank: Guru

bluelizard:

oh, sorry, the "me" must not go into [], because otherwise access thinks it's the name of a field, but actually, it's the form object... besides, since there aren't any spaces in the field name, i wouldn't put it in [], either. i'd write:

set_s(Me.GLcritTXT)

--bluelizard

02.19.2008 at 07:36AM PST, ID: 20929319

martywal:

I'm not sure where that goes?
I assume it should be in the public module?
But not sure where I have this;
Public Sub set_s(ByVal i As String)
s = i
End Sub

Public Function get_s() As String
get_s = s
End Function

02.19.2008 at 07:52AM PST, ID: 20929482

martywal:

I think I may know what you are trying to do now.
I have attached the "set_s([me].[GLcritTXT])" to the command button that opens the query but the resulting report is not reflecting the right data.

I would have thought gievn I am already referencing 2 criteria from text boxes:
HAVING (((GLExtract.BusinessGroup)=[Forms]![QryMonthRpt]![BGINtxt]) AND ((GLcodes.RCode)=[Forms]![QryMonthRpt]![INPUTtxt])
That it should be able to do a similar thing for this one.
THe value that is generated in the text box looks as follows: Like "6*"
Again though if I just enter this in the criteria under the field "DataItem" the query shows me what I'm expecting to see...
:-(

02.19.2008 at 08:14AM PST, ID: 20929696

Rank: Guru

bluelizard:

oh, ok, so the referencing of the text box is not the problem...

allright... note that there's a difference if you want to say
field="xy"
or
field like "x*"
in the query, because, as you see, in the latter case the equal sign is missing... so if you write

AND ((GLExtract.DataItem)=[Forms]![QryMonthRpt]![GLcritTXT])

and enter "Like 6*" for GLcritTXT, then access "sees" this:

AND ((GLExtract.DataItem)="Like 6*")

which will not generate the desired result... (it would only return records that contain "Like 6*" in their DataItem)

if you want to use "Like", you have to set up the query like this:

AND ((GLExtract.DataItem) Like [Forms]![QryMonthRpt]![GLcritTXT])

and then you can enter "6*" for GLcritTXT.

--bluelizard

Accepted Solution

02.19.2008 at 08:48AM PST, ID: 20930058

martywal:

Thanks Bluelizard.
I was sort of already onto that.
My criteria can have mutiple "Like" which is what is in the text box. Is there a way of just feeding the actual text in the text boxe straight into the query. It matches with SQL that way?
Thanks again

02.19.2008 at 01:33PM PST, ID: 20932835

Rank: Guru

bluelizard:

what do you do with the query? do you display the "normal" (built-in) query view, or do you use the query as a source for a report or a form? if, for example, you use the query as a source for a report, you can programmatically set a SQL statement as the report's source (instead of using a "stored" query) before you open the report (see example below).

in line 5, you can build the SQL statement like a normal string, i.e., you can use your text boxes as you want. e.g. if myfield holds "a like 6* or a like 7*", then the line
Reports("rptA").RecordSource = "SELECT * FROM mytable WHERE " & myfield
would result in this source SQL for the report:
SELECT * FROM mytable WHERE a like 6* or a like 7*

--bluelizard

                       
                                Private Sub button_Click()
  ' open the report in design mode (hidden)
  DoCmd.OpenReport "rptA", acViewDesign, , , acHidden
  ' set the desired source
  Reports("rptA").RecordSource = "SELECT * FROM mytable WHERE a like 'a*' OR a like 'b*'"
  ' save the report (no prompting)
  DoCmd.Close acReport, "rptA", acSaveYes
  
  ' now open it normally
  DoCmd.OpenReport "rptA", acViewPreview
End Sub

Open in New Window

02.20.2008 at 03:23AM PST, ID: 20936683

martywal:

Bluelizard.
I've managed to sort this out by making multiple text boxes that are refered to for each part of my Where statement example:
Like [Forms]![QryMonthRpt]![GLcrit1TXT] Or Like [Forms]![QryMonthRpt]![GLcrit2TXT]
It has meant changing the tables slightly but it is doing as I wanted to do.
I haven't tried your final post but I can see where you are going, Thanks.
It was post 20929696 that put me on the right direction, so I'll award points to that.
Thanks again for your help
Martywal

20080236-EE-VQP-29 / EE_QW_2_20070628