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 Replies


ADVERTISEMENT

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

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

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 5 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

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

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

Using An If Statement To Condition After Update

Oct 31, 2006

Hi, im very new to visual basic programming...what i am trying to do is implement an If Statment on AfterUpdate for my form:

If record_is_new Then

Dim db As DAO.Database
Dim RS As DAO.Recordset

Set db = CurrentDb()
Set RS = db.OpenRecordset("LTOT_second_oxygen", dbOpenDynaset)
RS.AddNew
RS![HospitalNumber] = Me![general_info.HospitalNumber]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
.........more of this kind of code

Else nothing
End If

any ideas on the vb code that would do this?

View 5 Replies View Related

Condition For Displaying Fields

Apr 27, 2008

I’ve created a report containing two labels and two text boxes. The “Record Source” for the report is Tbl_MasterList2. The control source for both text boxes is named field4 and field5, which are based in Tbl_MasterList2. As a result, my report displays all records from Tbl_MasterList2, field4 & field5 including those, which are blank.

How do I write an expression in my report that will allow me to display the values in field4 if filed2 = stringXYZ?

View 4 Replies View Related

Output To File Where Condition

Aug 2, 2005

I am using a where condition on printing records from a form to restrict output to the current record. I would also like to write the current record only to a file (rtf). Have set up an output to file button which works but writes all records and there is no code option for a where condition.

I can live with this as it is only for backup but a report per record would be a neater solution.

Is this possible anyone know?

Andy

View 4 Replies View Related

Modules & VBA :: How To Convert Where Condition

Dec 15, 2013

Everything I have goggled and tried doesnt work How do I convert the where condition to VBA

Code:
Form name frmMap
View form
Filter name
Where condition [chid]=[forms]![frmCurchesAll]![chid]

View 12 Replies View Related

Modules & VBA :: Validation Two Condition

Apr 3, 2014

I have one case about validation in the form

example: I have entry in table this mention below:

No.Register | Code_machine
________________________

001 | X01
002 | X01
001 | X02
003 | X02
003 | X01
002 | X03

How to make validation for this case when I will submit the data 001 and X01, the system will be cancel and show warning message : "Data Duplicate!".and of course with this happen, the user will be know what they will submit, it have been submit into table before by other user. if I will do make with one validation like noregister as checking data and use the DLookup, this is no problem.

View 6 Replies View Related







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