Multiple Condition IIF

Nov 13, 2007

I can't get this expression in Query to work! Any suggestions?

Account Type: IIf([parent account name] like “*hosp*”,"Hosp",IIf([parent account name] like "*vet*”,"Vet",IIf([parent account name] like “*dds*”,"DENT”,IIf([parent account name] like "*dmd*”,"Dentist”,IIf([parent account name] like "*pharm*”,"Pharm”,0)))))

as you can see I am doing search and identify for a new field.

I get an error message "the expression you entered contains invalid syntax"

View Replies


ADVERTISEMENT

Multiple Condition Query

Feb 4, 2008

Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?

View 7 Replies View Related

General :: Sum IIF With Multiple Condition?

Jun 4, 2014

How will I make a IIF formula with sum if 3 conditions are are true. I am trying to make a query which sum the Lbs Produce if date, Product Id and location met.

View 6 Replies View Related

Modules & VBA :: Dlookup With Multiple Condition

Apr 8, 2015

I Have one main form in access 2003 i want to use Dlookup function to 1 text box with 2 criteria is it possible?

My Formula:

Text67. Value = DLookup("Balance", "dbo_WbookEdit", "[Lotno] = '" & Forms![Edit]![LotNo] & "'" And " & [Description] = '" & Forms![Edit]![Description] & "'")

View 6 Replies View Related

Adding Multiple Searches In Where Condition

Oct 16, 2013

I currently have this for my where condition, and searches the form based on the clientID selected in the combo box.

The catch now is i have to add another search.

="[ClientID] =" & [TempVars]![ActiveControlValue]

now i have to also search [ProductID]

The combo Box is set up like this:

ClientID....ContactName....ProductName....ProductI D
Hidden.....Visible..............Visible........... ..Hidden

How can i add [ProductID] into the existing where condition to add an additional parameter?

View 4 Replies View Related

Duplicate Table Records Multiple Times According To Condition

Apr 4, 2012

I have one table like:

ID Type No
-----------------
1 FL 2
2 DL 3

I need the following table to be created using first table , each record should be duplicated according to value in No field:

ID Type No
-----------------
1 FL 2
1 FL 2
2 DL 3
2 DL 3
2 DL 3

View 2 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

An SQL Str Using IF Condition

Sep 19, 2005

Good morning


I am hoping some one can help me out with an SQL string I am having problems during to get working.

This is my code

Private Sub SearchRoleSeeking()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strFilterSQL As String
Dim txtBoxValue As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySearch")

lstSearchResults = txtSearch1

If Me.txtSearch1.Value = "Temp" Then
txtBoxValue = 1
End If
If Me.txtSearch1.Value = "Perm" Then
txtBoxValue = 2
End If
If Me.txtSearch1.Value = "Temp or Perm" Then
txtBoxValue = 3
End If
strSQL = "SELECT tblPersonalInformation.[PersonalID],tblPersonalInformation.[Surname],tblPersonalInformation.[Forename],tblPersonalInformation.[DOB],tblPersonalInformation.[WantedRate],tblPersonalInformation.[WantedSalary],tblPersonalInformation.[Status],tblPersonalInformation.[RoleSeeking]" & _
"FROM tblPersonalInformation " & _
"WHERE tblPersonalInformation.RoleSeeking = ('txtboxvalue')"


qdf.SQL = strSQL
Me.lstSearchResults.RowSource = "qrysearch"

Set qdf = Nothing
Set db = Nothing

End Sub


let me try explain what I am trying to do.
I have a form where personal information is entered into. It has an option group that allows 3 choices.
I have made a form that I will use to search (the above code is from that.) What I am trying to do is if a person enters "Temp" into txtSearch1 then for the SQL statement to search "tblPersonalInformation.[RoleSeeking]" for the value 1.
If a person enters "PERM then for the statement to use 2 as the value for txtSearch1 and so on.

Can anyone help out with my task at hand.

Thanks everyone for your help.

View 1 Replies View Related

Where Condition Help

Apr 6, 2007

Please don't criticize the naming, I've renamed to make the logic simpler to understand.

I am trying to run a query based on the value of a textbox. Right now if the text box is empty it shows all the records. This is what works:
SELECT A.ID, B.Weight
FROM A
LEFT JOIN B
ON A.ID = B.ID
WHERE B.Weight Like
IIf([forms]![Form]![txtBox] & ""="","*",[forms]![Form]![txtBox])

The problem is when tbl B is blank (and only when its blank), I also want to see all records where weight is null.

Some examples of what I've tried (I have tried variations of):
B.Weight Like IIf([forms]![Form]![txtBox] & ""="","* Or Is Null",[forms]![Form]![txtBox])
B.Weight IIf([forms]![Form]![txtBox] & ""="","Is Null or Like *",[forms]![Form]![txtBox])

I am not worried about the False condition, the problem is that Null is not being returned from inside an IIF().
When I set the WHERE statement to: WHERE B.Weight Is Null, it works. But if I try IIf([forms]![Form]![txtBox] & ""="",Is Null,[forms]![Form]![txtBox]). It doesnt work (Whether I put it in quotes or not).


Any help would be appreciated. Thank you.

View 1 Replies View Related

Sql Command Or Condition

Jun 11, 2006

hi i was wondering if anyone can tell me a condition or a command in which a user will be able to enter only 6 digit number. like 123456. if he tries to enter 234 the database should not accept. is this possible also? any help would be great.
Thanks

View 14 Replies View Related

Evaluate A Condition

May 11, 2007

Please have a look at the following code (I am not good at VBA or SQL).

Me.[txtBox] = DLookup(“[SNum]”, “[Table1]”, “[RecID])
If Me.txtBox =>2 Then
Condition if true
Else
Condition if false
End If

I type a value in my [txtBox], a number. I want to evaluate if the typed value meets the condition or not. [SNum] is serially numbered and [RecID] is autonumber Primary Key. The above code is not working. How do I achieve this? Please help.

View 2 Replies View Related

IIF Condition With 3 Options

Sep 23, 2005

:o Please help... I am trying to get the following results. If the Qty is less than 99 bag 10 per, if the qty is between 100 and 999 bag 100 per and if the qty is greater than 999 bag 200 per.

Pkg Qty: IIf([QTY]<=99,"10 PER",(IIf([QTY]>99,"100 PER",IIf([QTY]>999,"200 PER"))))

I am only getting the results of "10 PER" AND "100 PER". How do I get the results I want?

View 5 Replies View Related

Yes/no Condition For Query

Mar 1, 2006

i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..

note..the bold bit is the bit i am questioning.

WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"

or something like this??

View 3 Replies View Related

Yes/no Condition Not Working

Mar 2, 2006

i have created a search form. any word the user types it searches certain details in my enquiries.
in my form i have a list box that displays the enquiries sent by my query.

i have placed a Job_Booked (Yes/No field) checkbox in my enquiry table. this is because if the job is booked then i no longer want the record to appear in the enquiry list im sending to the form. ive tried this code, but even when i tick the job booked button, the enquiry is being displayed.. why is this


SELECT zEnquiries.Enquiry_Date, zEnquiries.Job_Date, zEnquiries.Job_Day, zEnquiries.Job_Time, zEnquiries.Enq_Forname, zEnquiries.Enq_Surname, zEnquiries.Pickup_Add, zEnquiries.Pickup_Post, zEnquiries.Dest_Add, zEnquiries.Dest_Post, zEnquiries.Quote, zEnquiries.Car, zEnquiries.Passengers, zEnquiries.Luggage, zEnquiries.Notes, zEnquiries.Job_Booked, *
FROM zEnquiries
WHERE (((zEnquiries.Enq_Forname) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Enq_Surname) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Job_Date) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Enq_Phone) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Dest_Add) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Dest_Post) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Pickup_Add) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Pickup_Post) Like '*' & Forms!Enquiry_Search!Search2 & '*')) And zEnquiries.Job_Booked=False
ORDER BY zEnquiries.Enquiry_Date DESC;

View 2 Replies View Related

Where Condition In Report

Mar 13, 2005

i have a form of Employee Info, where with navigation button i move to the next employee.
i have put a Command button to Preview Report on Single Employee Info that i am reading currently.
So i want to Filter the Report with Single Employee Name and records
so im coding on click even of the command button (see below) and its giving me null report.

im using where condition to filter the report and assign to it a variable employee name from the form. Reminding that source codes of both Form and Report is the same

X = Form_frm_MainCurrent.EmployeeName.Value

stDocName = "rpt_All"

DoCmd.OpenReport stDocName, acPreview, , "EmployeeName = X"

anyone got any clue to help me out?

Thanks for your time

View 2 Replies View Related

Checkbox As A Condition

Mar 8, 2006

i'm making form from query. but all i need to do is calculate days worked (TimeService) either worker still working or resigned.

i want to know is it possible to make it condition like it to query? if not can u tell me the other way?

View 2 Replies View Related

IF Condition On A Form

May 9, 2006

Hi everyone, i have a form for bookings. People book by entering (among other stuff), the date and the booking slot.

Basically, what i need to do is have it so that only certain booking slots can be chosen on differnt days... for example, the booking slot of "Eve Extension" can only be used when the date refers to a Friday or Saturday, any other days it would not be allowed.

Is there any fool proof way of doing this, keeping in mind i dont exactly know VBA at all....

cheers, dragon

View 3 Replies View Related

Condition For New Record

Sep 9, 2006

What method(s) are available to detect when a user is at a new record? I really just want to change the .text property of a combo box when the user is creating a new record. If this sounds stupid, I can explain the specifics.

View 6 Replies View Related

Modules & VBA :: AND In Where Condition

May 21, 2014

I got this code to run correctly, which pulls records where they are not junk(can be seen at end of where condition), the only issue is that the "AND" does not turn blue. Is this a problem?

Code:
If Not IsNull(Me.txt_last_rec_id) Then
'DoCmd.SetWarnings = False
SQLText = "INSERT INTO tbl_batt_id " & _
"(record_num, bat_id, manufacture, date_code, barcode, status) " & _
"SELECT " & Me.record_num & ", bat_id , manufacture, Date_Code, barcode, Status " & _
"FROM tbl_batt_id " & _
"WHERE record_num = " & Me.txt_last_rec_id & " And [Status] <> 'Junk'"

[code]....

View 3 Replies View Related

Where Condition Larger Than 255

Feb 24, 2015

I'm trying to put in a Where Condition for an ApplyFilter action, but it only allows up to 255 characters, I need more. I have very little experience using code!I want this to be the Where Condition:

Code:
[Program Name] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Organization] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Program Type] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office City] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office Province] Like "*" & [Forms]![Program List]![Text34] & "*"

How do I make this into the Where Condition? An full example would be nice.

View 7 Replies View Related

Combobox List Condition

Jun 13, 2006

how do i make 2nd combobox list based on 1st combobox list, mean if i choose A at 1st combobox, only A1-A5 option appear, and when i select B at 1st combobox, only B1-B8 appear to select at 2nd combobox..

TQ

View 1 Replies View Related

Format Query If Condition Is Met

Jun 21, 2005

hi ppl,
Could someone please help me with a query that I'm trying to create.
I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others.
e.g:

Length = 3
DateCleared = 31/01/2001
DateRenewal = 30/01/2004
--- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.

thankyou.. :( :confused:
Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.

View 3 Replies View Related

Count Cells With Condition

Jul 1, 2005

I need to count the number of cells that match a certain number like >0 and <60. Thanks!,
Mike :confused:

View 2 Replies View Related

Unmatched Query With Condition

Jan 9, 2006

Hi there, I currently have the below tables:

tbl_stores
ID NAME
123 Store A
456 Store B

tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999

I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B

My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.

SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store

Any ideas? Thanks for your help with this one!

Cheers

Tony

View 2 Replies View Related

Enter Address On Condition

Sep 14, 2005

I have written some code that adds the address to the box dependant on base, but i get the error message

Compile error:

end if without block if.

Private Sub Report_Open(Cancel As Integer)
If Me.Base = "STN" Then Me.Address = "address here"
End If

End Sub

Can anyone explain this for me

View 1 Replies View Related

Open Form With Condition

Mar 25, 2006

I have searched, but I cant find a thread for this.

I have a calendar that I input when people are going to be off. If there is nothing for that date then the record is blank. I have a form that opens when the db opens attached to the date on the switchboard. If no one is off then the form opens blank because there is no date in the table to match. What I want to do is have a something built into the code that says if there is no match (null??) then dont open the form.

DoCmd.OpenForm "PopUpCalendar", acNormal, , "[Calendar]![Date] = Forms![Switchboard]![MainDate]"

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved