Visual Basic :: MS Access Select Query Criteria

Using Multi-Select List Boxes As Query Criteria
Hello I am relativley new to programming and have run into a situation where I could use some assistance. I have looked for articles based on using multi-select list boxes, but can't seem to find some good explanation on how to capture data from the list box for criteria use.

I am using a multi-select list box on a form in vb 6.0. I need to write code to capture the items the user has selected to carryover to another list box. ( Just to make sure I am clear I have two list boxes one filled at runtime with all fifty states and abbreviations, the user can click and Add cmd button to add the state to the other list box which I titled lstCriteria)

I need to capture the (left ,2) of the Items in the lstCriteria box for use as Critieria for a SQL Query.

Here is how my code starts:

Variables:

Dim strStates as String
Dim intCounter as Integer

If lstCriteria1.ListIndex = -1 Then
MsgBox "State Criteria Not Used", vbOKOnly, "State Criteria"
ElseIf lstCriteria1.ListIndex >= 0 Then
For intCounter = 0 To lstCriteria1.ListCount - 1
If lstCriteria1.Selected(intCounter) = True Then
If strStates = "" Then
strStates = "[State] IN ----------------------------------

There is where my issue lies, I initially had the follwoing as code for the "--" line:

"[State] IN '" & Left(lstCriteria1.selected(intCounter),2) & "'

I know I am not using the correct property for the above highlighted piece, but I have tried multipe properties from the list, and can't seem to get it to work

Any Assistance is appreciated, and thanks in advance for helping the newbie.

Pls Hlp Urgent, Vb Textbox.text As Criteria On MSaccess Select Query?
Dear all,please help
I create an aplication that connect to an MSaccess database using data environtment.
how to use a text on textbox become a criteria on the MSaccess query.

for example as an ilustration:
DE.rsschedule is an ado DE command connect to Schedule query in MSaccess database
Frm_schedule is a vb form with a textbox , a command button, and a frame contain some text boxes (databound to DE.rsschedule ) with some record navigation button.

when a teacher anter their name into the text box then click on command button then the textbox.text is use as a criteria
on Schedule query. so the query give every record on Schedule table where the teacher is = textbox.text
then the result of that Schedule query is use as the databound on the text boxes inside the frame.

so the teacher can use the navigation button to see on the text boxes inside the frame their teaching schedule today

any body please help,
may be any of you have a different approach to solve this problem

Edited by - kjoephi on 10/25/2004 7:12:55 PM

Access And VBA Query Criteria
Hi --

I am working with Microsoft Access 2000 SP3 and am having some difficulty ( still new to Access and VBA) . So, I only have "Views" to create my queries from, and the interface provided is not extremely intuitive.

I have created a view (query) that pulls from two tables and connects them using a FK. I want to filter the records that are shown by only having 3 record id's (from many available).

There is a "Criteria" option but I am not sure how to make the criteria : thisRecord & thisRecord & thisRecord .

What do you think is the easiest solution?

- Mike

Access Query By Month Criteria Help?
=DatePart("m",[Text56]) =1

"the exppression you entered contains invalid syntax"
what am I entering wrong?

I am trying to run a query by putting this into the criteria of the date I am trying to search. Text56 is the search box that the person enters the date. Anyway I want it to just search by the month. so if they entered 2/19/2003 into Text56 it would would search my Date for February.

Thanks

Access Append Query Criteria
Got this huge Oracle DB, can't do any Oracle programming here, using ODBC to connect with Access 97 DB, need to populate local tables.

ClientData table has OfficeID field; no problem there; we can use our local OfficeID as criteria for the append query, so we don't have to pull the whole universe out of the big DB.

Other tables with Client-specific data don't have the OfficeID field, so we have to use criteria on the ClientID field. First I ran the append query for the ClientData table. Now, I want to run an append query for some of the other Client tables, and I want to set criteria for the ClientID field that makes sure the ClientID exists in the main local ClientData table before appending the row out of the auxiliary Client table in the Oracle DB to the aux. table in the local DB.

All I could think of was to write a function to use in the Where clause. I can't tell if it's working, and it's much too slow anyway.

Maybe there's a different way to do this:

Code:
Function LocalClient(strID As String)
On Error GoTo Whoops

LocalClient = CStr(Nz(DLookup("CLIENT_ID", "tblAPPOWNER_CLIENT_DATA", _
"[CLIENT_ID]='" & strID & "'"), 0))

OffRamp:
Exit Function

Whoops:
MsgBox "Error #" & Err & ": " & Err.Description
Resume OffRamp

End Function
BTW, almost everything (including Boolean) is pulled in as text from this monster DB, which we haven't found anyone to explain to us yet.
Anyway, here's what Access wrote for me when I put LocalClient(CLIENT_ID) in the criteria row in the CLIENT_ID column of the query design grid:

INSERT INTO tblAPPOWNER_DESIRED_JOB_LOCATION ( CLIENT_ID, DESIRED_LOCATION_ID, STATE, COUNTY_NAME, CREATED_BY, MAINTENANCE_USER, MAINTENANCE_DATE )
SELECT APPOWNER_DESIRED_JOB_LOCATION.CLIENT_ID, APPOWNER_DESIRED_JOB_LOCATION.DESIRED_LOCATION_ID, APPOWNER_DESIRED_JOB_LOCATION.STATE, APPOWNER_DESIRED_JOB_LOCATION.COUNTY_NAME, APPOWNER_DESIRED_JOB_LOCATION.CREATED_BY, APPOWNER_DESIRED_JOB_LOCATION.MAINTENANCE_USER, APPOWNER_DESIRED_JOB_LOCATION.MAINTENANCE_DATE
FROM APPOWNER_DESIRED_JOB_LOCATION
WHERE (((APPOWNER_DESIRED_JOB_LOCATION.CLIENT_ID)=LocalClient([CLIENT_ID])));

Updating Access Query Table With New Criteria
I am using VB to access data in Microsoft Access. I have created a query table in Access. Through VB, the user chooses the quarter and year. The query should be updated to reflect this giving the data for that time period.
Unfortunately I am of the old school and not very good with using SQL statements. Please help!

Quartervar = Quarter.text & QYear.text

Set myrecordset = mydatabase.OpenRecordset("UPDATE [Effort Allocation Query 1] WHERE Quarter = '"Quartervar"'")

I keep getting a syntax error and have tried using different ways to get it to work.

Access <-> VB Interaction. Inserting Query Criteria.
I am using VB6 with MS Access 2003.

As i have been able to use [Insert Week Number] in the criteria of a query for the user to specify the week to be shown, could i have VB code insert this data automatically from what the user of the GUI has requested?

Thankyou,
Michael

Edit: Is there anything like a sendkeys()?

VB6/Access Query - Criteria By User Input
hiya...

i'm new to vb6, and i'm creating my first real application and I'm beginning 2 think i'm going mad bcoz of this...

I'm creating a 'simple' app in vb6 that requires a connection to Access. I'm using Data Controls for this (ADO). So far so good, except I can't create a search in vb6 that allows the end-user of the app to type in the specific criteria(on) he wants to search for.

E.g. there's a table with a list of students and their details (tblStudentDetails) and another one with all the marks they receive for specific assignments (tblMarks). The search that the user wants to carry out requires him to input the name of the specific assignment, and the date it was given, from the table tblATDetails. The app should then be able to find all the information related to this specified criteria, from the appropriate tables in Access, and display it in a screen object in vb6.

How do I do this?

Any help on this will be MASSIVELY appreciated...

Thanx in advance...
Alice

Running An Access Query Using A Form Field For Criteria
Scenario:

Access 2000 DB with several Link tables pointing to a SQL DB. Link via ODBC. Access Form gets input for query criteria.

Need to use VB to output the results of a query to MS Word. The Access report and associated query work great. However, my VB code will not open the query without an error.

My sequence of events is:

User sees Access switchboard, navigates to the button for the desired report. That button opens the form, frmRMABill. the OK command button on this form calls the following module:

Sub RMABilling()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rstPartBilling As ADODB.Recordset
Dim rstLaborBilling As ADODB.Recordset
Dim rstHeadBilling As ADODB.Recordset
Dim cn As ADODB.Connection
Dim qryPart As String
Dim qryHead As String
Dim qryLabor As String
Dim partTotal As Single
Dim hourTot As Single
' Open Word Document
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set doc = objWord.Documents.Add("h:RMAbilling.dot")
' HEADER QUERY
qryHead = "SELECT dbo_RMA.ID, dbo_RMA.NEW_CUST_ORDER_ID, dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID, dbo_RMA.IS_WARRANTY, dbo_RMA.LAST_RECEIVED_DATE " & _
"FROM dbo_RMA LEFT JOIN dbo_DEMAND_SUPPLY_LINK ON dbo_RMA.NEW_CUST_ORDER_ID = dbo_DEMAND_SUPPLY_LINK.DEMAND_BASE_ID " & _
"WHERE (((dbo_RMA.ID)=[forms]![RMA_NO]![RMA]));"
Set cn = CurrentProject.Connection
Set rstHeadBilling = New ADODB.Recordset
With rstHeadBilling
.Source = qryHead
.ActiveConnection = cn
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.Open Options:=adCmdText
End With

I am wondering if I need to address the criteria field in my input form witihin the code for this module or if my order of events is wrong. Compiler gives me an error:

No value given for one or more required parameters.

Any suggestions are greatly appreciated.

Thanks,

Select Query In MS Access
Can any one help me out what is wrong with the below query
in MS Access ....

select muid, ExcCode,TotExcCount from
(select muid,ExcCode,sum(ExcCount) as TotExcCount from TimeU where rowDate between #2003-12-02# and #2003-12-03# and server = 'iextest1' Group By muid,ExcCode) TempTable
orderby muid asc

The query works fine till Group By and fails when I am trying to do
Order by

Thanks...

Select Query Between VB And Access
i would like to select a field from a table.(The field i want to select is called Pmodel)
but it keeps giving me an error of (very few parameters)
where i want the selected number displayed in a test field.
I have two table users and printers..
the user has a foriegn key to the pinters table which is the
printers ID number..its called PID in the Users table and ID in the
printers table.

and the code is as follows:

Set rsModel = dbIT.OpenRecordset("SELECT Pmodel from Users,Printers where rsUsers!Snum = staffNumber AND rsPrinters!ID = rsUsers!PID and rsUsers!ID='?' and rsUsers!Sname='?' AND rsUSers!DeptName='?' AND rsUsers=!Pid='?' AND rsPrinters!Bnumber='?' AND rsPrinters!Cnumber=?")

txtModel.text= rsModel!Pmodel

Would u help me plz to get the needed information from a table...
i really appreciate your help..thank u

Access Combobox Select Query
I am working on an Access database with 2 comboboxes that have the following tables associated with it:

Table : class
Field : index
Field : classID
Field : categoryID

Table : category
Field : index
Field : categoryID

What I am trying to accomplish is this:

cbocategory = category.categoryID

cboclass = class.classID

However, for cboclass, I want to create a query where after you've selected cbocategory, it would then give you classes that are allowed for that particular category. For example:

Code:
SELECT DISTINCT classID FROM class WHERE class.categoryID = category.categoryID

Currently, this statement does not work. Does anyone have a better suggestion?

Select Query - Need To Know Access To Answer
Query syntax:
SELECT Models.Model
FROM Products INNER JOIN Models ON Products.ProductID =
Models.ProductKey
WHERE (((Products.ProductID)=[Forms]![subfrm2BD]![Item]));

This is the rowsource property for a combo box which is on a continuous
subform and should populate according to the Item field of the current
record in the subform. The form's parent is 2BD, the subforms name is
subfrm2BD, and there is only one Item field in its recordsource. The
tables Models and products relate on the ProductID(Unique) and
ProductKey(non-Unique) fields, Models lists all models for a particular
product, and the Item field refers to a particular product. There is
also an Item field in the Products table with the product name. There
is a line in the combo box's Enter event combo.requery to refresh the
contents. When the subform is opened alone, everything works fine, the
combo retrieves the models for the item, but when it is opened within
it's parent, when the Enter event fires, an Access inputbox pops up with
the prompt "Forms![subfrm2BD]![Item]" as if Access does not recognize
the expression.

VBA Access Select Query Set To Variable
Good afternoon people.

I am trying to create a select query in VBA and assigning the result of the query to a variable.

I am using Access 2003 so I beleive I need to set up an ADO connection versus a DAO.

User = Environ("USERNAME")

SQL = "SELECT [tbl_PC&A].[Security Level] " & _
"FROM [tbl_PC&A] " & _
"WHERE (([tbl_PC&A].[RACF ID])=UCase(User));"

SQL should equal 1, 2, or 3 based on the user's id captured from the system.

Do anyone know how I can make this work?

Thanks in advance, DAVE

Select Query For Access 2000
Hi All,
I m using access 2000 and having a table in which there are some fields of data type double with rounded off upto 2 decimal points. But actully I want to round them upto 0 decimal point to show the client. But the select query for this doesn't work. this is my query

Select Round(Salary,0) from Employee

where datatype of salary is double.

when i run this query through query builder in access it gives correct result but when I run this query through my vb program it gives me following error:
"Undefined function Round"

this error comes whenever I use eithere built in function or user defined funtion

can anybody help me?

How Can I Map Oledb Parmeters Into A Access Select Query
PARAMETERS cod Text ( 255 ), nam Text ( 255 ), num Text ( 255 ), addr Text ( 255 );
SELECT [INTERNATIONAL RATES].[COUNTRY CODE] AS CountryCode, [INTERNATIONAL RATES].[COUNTRY NAME], INTERNATIONALSUBSCRIBER.NAME AS InternationalContactName, INTERNATIONALSUBSCRIBER.Address AS InternationalContactAddress, INTERNATIONALSUBSCRIBER.ContactNumber AS InternationalContactNumber
FROM [INTERNATIONAL RATES] INNER JOIN INTERNATIONALSUBSCRIBER ON [INTERNATIONAL RATES].[COUNTRY CODE] = INTERNATIONALSUBSCRIBER.COUNTRYCODE
WHERE ((([INTERNATIONAL RATES].[COUNTRY CODE])=[cod]) AND ((INTERNATIONALSUBSCRIBER.NAME) Like "*" & [nam] & "*") AND ((INTERNATIONALSUBSCRIBER.Address) Like "*" & [addr] & "*") AND ((INTERNATIONALSUBSCRIBER.ContactNumber) Like "*" & [num] & "*"));

this is an access query.when i ran it from access it worked fine.To work fine this query must be given the value of cod parameter rest can be left empty
i.e
cod=some string
nam=can be empty
num=can be empty
addr =can be empty
now i have declared four parameters

.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@cod", Data.OleDb.OleDbType.WChar, 13))
.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@nam", Data.OleDb.OleDbType.WChar, 30, ParameterDirection.Input))
.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@num", Data.OleDb.OleDbType.WChar, 30))
.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@addr", Data.OleDb.OleDbType.WChar, 70))

here txt means a text box,cmd combo box ds is a dataset
now values to these parameters are given
.Ada.SelectCommand.Parameters(0).Value = Ds.Tables("Country").Rows(CmbCountryNameCode.SelectedIndex).Item(0).To String
If ChkName.Checked = True Then
.Ada.SelectCommand.Parameters("@nam").Value = TxtName.Text.ToString
MsgBox(.Ada.SelectCommand.Parameters("@nam").Value, MsgBoxStyle.Critical)
End If
If ChkAddress.Checked = True Then
.Ada.SelectCommand.Parameters("@num").Value = TxtNumber.Text
End If
If ChkNumber.Checked = True Then
.Ada.SelectCommand.Parameters("@addr").Value = TxtAddress.Text
End If
now when i execute a fill
.Ada.Fill(.Ds, "InternationalNumbers1")
no records are there in InternationalNumbers1 but when i run query in access based on same values it returns records.i think the other three parameters are not mapping properly can some one help

MS Access Query - Select Row With Lastest Date
Hi,

I have the following two example rows in a table...

acc# expdate amt1 amt2
234 6/8/07 5000 10000
234 1/9/07 4000 12000

I want to select the row for with the lastest expdate (the first row). In this table there are thousands of records and I would like to pull out the lastest entry for each acc#. Any help will be much appreciated.

Thanks,

Derek

SQL Select Works With ADO But Not In Access Query Window
I have an sql select that works fine when I run it with ado from my VB6 program. However, when I try to run the same select statement in the Access DB that is called by the ADO in the VB6 program, I get a circular reference error.

Does that make any sense?

Here is the sql statement:

SELECT Format(SFTimingFrom,'MM/DD/YY') & ' - ' & Format(SFTimingTo,'MM/DD/YY') as SFTimingFrom ,Right(SFTxt,(Len(SFTxt)-LenFileExtName)) as SFTxt,SFImpression ,SFElement, SFDetail FROM SponsorCatAndFileInName WHERE SFTxt <>'' AND CatID = 22 AND NameID = 13 AND SponsorID = 56 ORDER BY SFTxt ASC

Here is the ADO that calls it and works just fine:

Set myRS = New adodb.Recordset
Set myRS.ActiveConnection = adoconn
If (pagesize > 0) Then
myRS.pagesize = pagesize
End If
myRS.CursorLocation = adUseClient
myRS.Open sqlTxt

Here is the error I get when I run it in the Access query window:

"Circular reference caused by alias 'SFTimingFrom' in query definitions select list"

How To Create A Select Query In MS Access Programmatically
Dear Friends,

I wish to ask how can I create a select query (not SQL) across multiple tables in an Access database using commands or code from my VB program and how to delete it afterwards from the database as it just serves as a temporary buffering purpose for export.

VBA In Access - Run Query (SELECT FROM ...) And Get Recieved Data
Hey.

I'm having a problem with VBA in Access 2003.

I want to run a SQL query, but don't know which function to use.

I have tried CurrentDb.Execute and CurrentDb.OpenRecordset with no luck.

I wan't to run this query and get the returned values in a variable in VBA.
SELECT Rad, Plass FROM tabell WHERE ForestillingID=1 AND Rad=5

Any ideas?

How To Run A Select Query In VBA (Access) And Store Values In A Variable
hello

i want to run a select query in VBA (Access) and want to store values in a variable like

str = "select CountryCode from Country where CountryName = '" & Combo22.Text & "';"

here combo22.text is the name of combo bo from where the value for where clause will b selected . now after getting this value i want to store it in a variable and then want to use it in insert query.

like

DoCmd.RunSQL "Insert into table (val1,val2) values(txtval.Text,str);"

can anybody tell me how to do it in VBA
how to execute a query in access in VBA code

and second question is that how after execution of a query we can use that reults to poulate in a combo .

waiting for an early reply
regards
Naheed

Criteria In Select Statement
Hi Guys
This is what I have;

Where NameID = " & (frmMain.txtNameID.Text) & " And TypeAccount = Like " & (cboAccount.Text) & " And DepositDate >= #" & DTPicker1.Value & "# And DepositDate < #" & DTPicker2.Value & "#" & vbCrLf & vbCrLf

The NameID and DepositDate works fine.

The problem I have is with the TypeAccount.
When I change the TypeAccount to a numeric value it works. The cboAccount display's a word "FlexiSavings" and this is where the (I think) Like statement needs to select the word Like FlexiSavings.

Any help Please!!!!!!!!
Thanks
Pieter

Edited by - pieterw0 on 1/16/2005 3:37:04 AM

Query Criteria
I have an Access 2000 Database with multiple queries I need to restrict the queries to between two dates how can I put the same date criteria into all the queries at once?

SQL Query Criteria
Hi I wonder how could I change this in order, that I could search this DB with similar words (not just "King", but allso "ing" or "Kin")
SELECT LastName, FirstName FROM Employees
WHERE LastName = 'King';

tx.
matej

Criteria For A Query
I’m pretty new to this so forgive my lack of correct jargon. I’m trying to set criteria in a query to pull data according to a checkbox (chkBorrowed) on a form. If the box isn’t checked, I want the query to pull both true and false records. If it is checked, I just want it to pull the true records.

I get a message saying, “The expression you entered has an invalid .(dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or typed parentheses following the Null constant.”

Am I missing something in this expression or am I totally off and need to do something else entirely?

if[forms]![frmCDReport]![chkBorrowed]=true (SELECT[tblCD-ROMinfo]![Borrowed]=true) else (SELECT[tblCD-ROMinfo]![Borrowed]=false AND select[tblCD-ROMinfo]![Borrowed]=true)

Thanks!

Select Records Based On Date Criteria?(not Solved)
HI all,
here is a situation where i need all your help.

i have to find all the records which are 3 days old, 5 days old, and greater then 5 days old. After i get all this records i am filling them in mshflexgrid and colour the text of each row in different colour depending upon the above criteria.

Filling and colouring is not a problem.i can do it. the issue is how do i find recrods based on todays date that are 3,5 or > 5 working days old (weekends are not included).

ok for example i have a entry of a record on friday. Now when i look at it on Tuesday it should fall in criteria of 3 days old as the difference in working days is just 2 days.

Any logic and all the help would be really great.

Thanks

Below is the code that i am using---(In this there is no checking for Weekends)

----fg = mshflexgrid.

For i = fg.FixedRows To fg.Rows - 1


            sDate1 = DateAdd("d", -3, Date) ' diff. of 3 days
            sDate2 = DateAdd("d", -5, Date) 'diff of 5 days.
            sDate3 = DateAdd("ww", -1, Date) ' diff of 1 week
            If Len(Trim(fg.TextMatrix(i, 4))) > 0 Then
                Date1 = Format(fg.TextMatrix(i, 4), "dd/mm/yyyy")
            End If
            If Date1 >= sDate1 And Date1 <= Date Then
                For j = 0 To 4
                    fg.Col = j
                    fg.Row = i
                    fg.CellForeColor = vbBlack
                Next j


            ElseIf Date1 >= sDate2 And Date1 < sDate1 Then
                For j = 0 To 4
                    fg.Col = j
                    fg.Row = i
                    fg.CellForeColor = vbBlue
                Next j

            ElseIf Date1 <= sDate3 Then
                For j = 0 To 4
                    fg.Col = j
                    fg.Row = i
                    fg.CellForeColor = vbRed
                Next j
            End If

Next i

Mits

Edited by - Mits on 8/29/2003 12:52:51 AM

Query Criteria For Recordset
I've been using Access97 right out of the box for about 4 years, getting very comfortable with forms, reports, Access queries, etc. Now I have the need to work with data directly in code, and I need a little help.

I'm accustomed to setting critera in the Access query design grid that refer to values on forms, to generate reports, etc. But I'm finding that doesn't work with a recordset. If I open a recordset using for the source a query that gets a criteria value from a form, I get an error: "Too few parameters. Expected: 1." This apparently means that referring to a value on a form doesn't work for a recordset? I've also tried using a public variable in a WHERE clause in a SQL statement, but this isn't working either.

How do you open a recordset using a temporary value for a query criterion? I must be missing something very basic here.

Adding A Criteria To A Query
I'm using Excel and Microsoft Query.

How do you add (or modify) a critera to a query using VB without coding the whole query in VB?

This is because someone else will be using the program and they have no knowledge of visual basic. So they will be using Microsoft Query to define their query.

I have a button in my worksheet that only refreshes the query (and does not define it) created by this user and it thus returns updated data. But what I want to do is, before refreshing the data, add (or modify) a criteria that will pertain to a specific date. This date can be changed by typing it into a certain cell on the worksheet.

It will go something like this:
Button is clicked, change the date critera, refresh the query.

Thanks in advance.

Jeff

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Query Criteria Problem
Okay, so I have this Report form that allows you to pick a report. If you pick a certain report, it will make three groups of radio buttons visible and enable them.

They are used to narrow down what type of licensees the report will show. In the report that it opens, it is fed data by a query. In the query it has a iif statement in the criteria.

Code:
IIf([forms]![frmReport]![frame9]=1,"Active",IIf([forms]![frmReport]![frame9]=2,"Expired",IIf([forms]![frmReport]![frame9]=3,Is Not Null)))

I have tried it multiple ways, but it doesn't seem to work no matter what.

Code:
IIf([forms]![frmReport]![frame9]=1,"Active",IIf([forms]![frmReport]![frame9]=2,"Expired",Is Not Null)))
It doesn't give me any error code, it just ends up pulling no records. Well, it's just the last option that is giving me trouble. It's got to be something with the syntax of what I am trying to do.

If I run it with [frame9]=1 or [frame9]=2, the query works perfectly. If I run it where [frame9]=3, that is when it fizzles.

Last Weekday In Query Criteria
I need some help with an Access 2k3 Query. I'd rather let Access do the thinking on this one, as other criteria are already in place.

I'm looking for the previous Friday. That's all - nothing tricky. I've tried so many combinations of iif's and now's and weekday's, I don't yesterday from tomorrow...

iif(weekday(now(),1)=1,now()-2,iif(weekday(now(),1)=2,now-3,...

Is there an easier way?

Query Criteria Option Box
Hi
Can anyone recommend a way to avoid the 'soft-crash' that happens when using the 'Cancel' option on an InputBox. This InputBox is the one generated by using the criteria option in a query.
I have considered not using the query facility and just using my own InputBox but using the existing one seems a better and easier option.
Can I set the vbCancel in these boxes to return to the main menu somehow?

Checkbox Query Criteria
I have a table (MS Access 97) with 12 records. One of the fields in my table contains a Yes/No checkbox.

When I query the table for records with criteria of Yes (for the checkbox) I get my 5 records which have the checkbox checked-off. Great. My problem is that when I query using criteria of No I get 0 records. Why am I not getting the other 7 records under this condition?

Sounds easy. Anyone?

SQL Query With Same Criteria On Several Fields
Hi,

I'm trying to understand why this is not working for me..
I'm using Access2000 database and ADO Object to show data over datagrid.
What I'm acually trying to do is to filter data according to the SAME criteria but on 5 different fields (some of the fields might contain a null value) named CRem1, CRem2 etc. - something like this:

CODE"SELECT * FROM Data WHERE [CRem1] LIKE '1%' OR [CRem2] LIKE '1%' OR [CRem3] LIKE '1%' OR [CRem4] LIKE '1%' OR [CRem5] LIKE '1%' AND Date >= #28/08/2006 00:00:01# AND Date <= #29/08/2006 23:59:59#"
Adodc1.Refresh
Adodc1.Recordset.Filter = sSql

Query Criteria From Table
How do I set the Criteria of a Query to that of text in a field on a table? The MSDN has suggested;
In("Canada", "UK") For a ShipCountry field, orders shipped to Canada or the UK
In(France, Germany, Japan) For a CountryName field, employees living in France or Germany or Japan

Which to me makes no sense as I don't have the database they are discussing! I had a brief look around for related info on this site but could not find any.

SQL Query Of A Same Criteria On Different Fields
Hi,

I'm trying to understand why this is not working for me..
I'm using Access2000 database and ADO Object to show data over datagrid.
What I'm acually trying to do is to filter data according to the SAME criteria but on 5 different fields (some of the fields might contain a null value) named CRem1, CRem2 etc. - something like this:

Code:
sSql="SELECT * FROM Data WHERE [CRem1] LIKE '1%' OR [CRem2] LIKE '1%' OR [CRem3] LIKE '1%' OR [CRem4] LIKE '1%' OR [CRem5] LIKE '1%' AND Date >= #28/08/2006 00:00:01# AND Date <= #29/08/2006 23:59:59#"
Adodc1.Refresh
Adodc1.Recordset.Filter = sSql

BTW, I'm not in the US so there is no problem with the date format..
Would appreciate any help solving this annoying problem!!
Thanks,
Yoav

SQL Select String Where Search Criteria Contains A ' Character? [RESOLVED FISH]
I have a surname in my DB, which is:

O'Keafe

But if I build a search SQL string like:

Code:
SELECT * FROM tblUsers Where Surname = 'O'Keafe'

then this gives an error

Any ideas?

Woka

Query Criteria Against All Values In Table
Hi,

I've got a list of clients in a table. Each of the clients is associated with one master.

e.g. Client Name joe bloggs uk
Master Joe Bloggs international

For each record there is a client name and the master (there are several records for each master).

Now in another query I want the user to enter the name of the master they want on a form, and the query will check the payment field against all the client names corrosponding to this master.
If the payment was made to a client associated with the master I want it to show.

Any help would be most appreciated.
David

***I think this was in the wrong place before I had it under VBA it's actualy a database issue***

Upddate Query With Multiple Criteria
Hi
I'm attempting to make an Access Update query that updates 2 different fields in a table, but using different criteria for each field. When I set it up how i think it should like in the query designer of Access it changes my criteria to an And statement, which I do not want. Here is how I believe the SQL code should look, but I'm getting a syntax error

Code:
UPDATE tblDailySalesTmp INNER JOIN tblSalesReport ON ([tblDailySalesTmp].[Chain]=[tblSalesReport].[Chain]) AND
([tblDailySalesTmp].[Sales Date]=[tblSalesReport].[Sale Date]) SET tblSalesReport.[Current Yr EVDY Net] =
[tblSalesReport].[Current Yr EVDY Net]+[tblDailySalesTmp].[Net Sales]WHERE (([tblDailySalesTmp].[Labels]="TOTAL EVERYDAY PRODUCT")
SET tblSalesReport.[Current Yr SEAS Net] = [tblSalesReport].[Current Yr SEAS Net]+[tblDailySalesTmp].[Net Sales]
WHERE ([tblDailySalesTmp].[Labels]="TOTAL SEASONAL PRODUCT"));
Any help would be great!

HELP URGENT DataEnvironment Query Criteria
Hello All,

I need help desperatly on this.

I have a form (frmCheck) and, in Designers, a report and a dataenvironment with a query to be used as recordset to the report. I need the query to take as a criteria a textbox value in the form frmCheck.
The user will have to fill the textbox in the frmcheck and then print the report, that will print the records according to the value of the textbox.
The query looks like this :
SELECT Checks.*, `Currency Code` AS Expr1
FROM Checks
WHERE (`Currency Code` = 'forms!`frmChecks`!`Text1`'),
but it is not working neither.
How can I make the query accept such a parameter.

Thank you in advance !!!!!
HELP ME !!!!!!

Populate Listview From Query That Has Criteria
im trying to populate a listview from a query in my database. only problem im having is, my query has criteria (Employee Number). how do i get the query to run given the user will click/select the employee number from another listview on another form? any suggestions?

Cheers

Date Criteria In Query Not Working
I have a table in Access with a date field. I want my application to retrieve all records where the date in the table is the same as the date a user selects. For some reason, although there is relevant data in the table that matches the user's selected date, I am unable to retrieve any of the records.
When I remove the date criteria and just select all records from the table it works. I have trying to get this to work for a whole day and it's not cooperating. I don't know what else to do.

I have tried using # and ' to enclose the date criteria but it still isn't working.

Here's my sql statement.

rs.Open "Select field_a, field_b from Departure_Times where Departure_Date= #" & SelectedDate & "#", Connection, adOpenKeyset, adLockOptimistic

Any suggestions?

Multi-table Query Criteria
I am using the same criteria on several queries;
   (SELECT[Field]FROM[Table])
Where the Field is ID and the Table is tblQryID.  A Visual Basic application sends data into the field then any records in the query whos field with the criteria does not match that of the on the criteria points to will get filtered out.
   This works absolutely fine provided the query only has fields from one table!

Using A Field In A Table As Criteria For A Query
I have a query set up that needs to get a criteria value from a field in another table. So far I have tried writing the criteria as:

=[table]![Field]

The query asks for the parameter so it's not reading the value in the field. I really have no idea how to set that up, is there a way of writing the criteria to reference the value in the field in the table. The table I'm getting the value from is not the table the query is based on.

Using Other Field In Table As Query Criteria
Hi, I have 2 fields FieldX, FieldY and they're both datatypes. Ive added these both to a query, but is there a way to query FieldX to find any numbers less than 10% of FieldY

For example
No, Field X, Field Y
1 80 1600
2 40 80
3 9 100

so record 1 and 3 will be shown as there numbers are less that 10% of fieldY's. Record 2 want show as it is not less than 10% of 80 (which is 8)

Thx

------------------------------------------------------------------------------
And when a man gets to heaven,
To saint Paul he'll tell:
1 more soldier reporting sir,
I served My Time in Hell
6 June 1944

2GB DDR400 RAM, 3.4Ghz EM64T P4 Socket 775, 200gb PATA HD 8mb cache, 500GB WD 16mb Cache (Master), 256mb 9950 ATI RADEON AGP ,19" LCD, and a 250W PSU

Running Vs6 Enterprise, running all vs8 products express edition

Edited by - Shandy on 3/5/2007 3:59:18 AM

Help To Create A Criteria??( Sql?/query) For A Report
hello all I have a form that has several buttons to access different criteria to create a report for my vb6 app. there is a datacombo box that you select a name and I want that (specific name) to only show on the report = that name(not all the names) with additional info from this report, such as date of the person and additional info about the person, just with that name. one of the sort needs to be the date that pulls up form the database, since this is supposed to generate from the access database using dataenvironment. I am un sure how to create the code for this, cus i want to pull the report when click on the commandbutton, selecting the name and showing all the rest seperating by the date.... How would i create this??? im not that familar with sql queries?? please help....
thanks
anna

Query In Data Environment With Criteria In A Form
Hello All,
I have a problem. I am doing a report in the Designer based on a query that is also in the data environment. the report should print a specific record (from ms access) based on a criteria that will be specified in a form (like for example in a textbox).
I wrote this sql :
SELECT Checks.*
FROM Checks
WHERE (`Currency Code` = 'Forms![frmChecks]![Text1.Text]')
but it isn't working !!!!!!!!!!
How can I reference a forms' textbox in sql ?????

Thank You in advance.

VB.NET - Using A Control's Value As Query Criteria With The Data Adaptor
I'm trying to specify a criteria in a VB.NET query within a Data Adaptor.
I just don't know what syntax to use....have tried all the VB6 stuff...
e.g
SELECT....WHERE cboACType = ' " & Forms!FormName!FieldName & " '
SELECT....WHERE cboACType = ' " & variable & " '

etc

does anyone have any clues ?

thanks,
Martin

Query Criteria Based On Unbound Textbox
Is it possible to set the criteria in my form based upon an unbound text box where multiple items are entered?

I have a list box that when items are selected it adds them to the text box i.e. if someone selects apple and pear from the list box the value of the text box is:

Apple Or Pear

If one or no item is pickedthen everything works as planned, but if multiple items are selected no records are returned. I beleive this is due to the query interpreting my criteria as:

"Apple Or Pear"

which would as expected not return any records. Where my problem lies, is how do I get around this and is it even possible?

Put Query Criteria In Report Header In Vb 6 Datareport
I show a data report based on a query in parameters in data environment. When the user views the reports I want to be able to to put a label "Report based on query by" and a textbox with the number that the query is based on. Basically for this I am having problems putting the textbox in the report header to show this. The number is from a database. I have selected the field in the data environment but I am not able to drag it onto the report header. When I copy and paste it to details then run the program I get a error that says textbox cannot be put in report header. Does anyone know how to go around this.