Iif Statement To Read Fields From Form Not Returning Values
Nov 4, 2006
Hey there!
I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D
Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.
The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.
I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.
For example for the invoices field have used:
IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])
So:
IIf(form checkbox = true, form text box value)
However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.
What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.
Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)
View Replies
ADVERTISEMENT
Dec 10, 2007
Hi,
I have a query which uses manual input on two fields. I would like to automate this query by pulling the input values from another table.
How do I read the input values from the other table instead of having the users type them in ?
Al
View 1 Replies
View Related
Nov 14, 2013
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
View 3 Replies
View Related
Oct 19, 2007
Hello,
Run-time error ...
No value given one or more required paramete
UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");
basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion)
3. For each InvoiceNumber AND GuitarItem.
NOTE: some invoices will not have any BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.
How should I rewrite the SQL to achieve this without getting this error?
View 14 Replies
View Related
Mar 10, 2015
I have a query that I set up to return multiple records. The number of the records is in that text box. I have tried to set the where clause to the text box but for some reason I don't get any of the records I want returned. I am using a Where... IN statement. I have tried to put the numbers in single quotes and no quotes and I have had no luck at all. If I do individual records they will return, just not when I try and do more then one at a time. This query is a part of something more, but since it doesn't return anything, Here is my sql statement
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In ([Forms]![ReportForm]![Text41]);
View 6 Replies
View Related
Aug 2, 2015
I have an issue that can be described as follows:
There is a textbox on a form and when I fill in its value, I would like it to lookup whether that value matches any of the values in another form and if it does present a message box. Both fields hold only numerical data, so no type mismatch issues.
I've tried the code following, but it only looks up the first value on the second form, not its entire dataset, which is problematic.
Code:
Private Sub Lot_AfterUpdate()
DoCmd.OpenForm ("Pot_Pot_ExtraLots")
If Me.Lot.Value = Forms!Pot_Pot_ExtraLots!ExtraPotLots.Value Then
MsgBox "There is a bag with extra sherds found during other analyses from this Lot! Lookup and combine results!"
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
Else
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
End If
End Sub
View 3 Replies
View Related
Jun 16, 2015
I am trying to sum a column on a subform named Cost of New if another column named Final = any of the values listed in my code. However, it is summing the Cost of New column regardless of the values of Final. It is summing for all values.What do I need to modify to make this correct or should I do it another way?
=IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Sum([Cost Of New]),Null)
View 3 Replies
View Related
Mar 7, 2013
I am trying to delete from a form, the record in the JD SOP TBL where the record Job Description matches the Combo Box "Job Description" on the form, and the Required SOP in the table matches the selection in the list box "SOP List". The list box has multi select to none and both fields are text fields.
Code:
DoCmd.RunSQL "DELETE FROM [JD SOP TBL] WHERE [Job Description] = '" & [Job Description] & "' " And [Required SOP] = " & SOP_List & "
View 5 Replies
View Related
Jan 10, 2012
I've built an IFF expression that is determined by a number of variables.
If any of the Data fields are filled I want the statement to return true.
If NONE of the four are filled in I want it to return false.
As it is, it returns true no matter what.
Here is the expression I'm using:
Title: IIF (IsNull([Data1] and [Data2] and [Data3] and [Data4]), True , False)
View 7 Replies
View Related
Jul 20, 2007
Hi anyone!
I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.
I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.
Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?
Thanks in advance
View 2 Replies
View Related
Nov 28, 2007
Hello!
I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
View 10 Replies
View Related
Jan 21, 2006
Hi.
Can someone help me?
I am trying to make a query return all values (please see attached jpg)
It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?)
In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.
Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.
Many Thanks.
Frank.
View 2 Replies
View Related
Sep 29, 2005
I have a problem that I haven't seen discussed here yet. (I know this is not good practice, but we're doing this for transfer to another application that wants and accepts data this way)
1) I have a multi select list box.
2) I have code that saves all the values in a semi colon delimeted list in a single field in the DB. (As I said, the application that this data is going to (and from) wants and accepts data in this way)
3) My problem is this. When I re-open my form, how do I get the values that are stored in the database to be highlighted in the multi select list box. I have code similar to this running, but still can't figure it out.
Dim lst() As String
rst.Open sSQL, cnn, adOpenStatic, adLockOptimistic
lst = Split(rst(0), vbCrLf, -1) 'Split the recordset into an array
For i = 0 To List96.ListCount
For j = 0 To UBound(lst) 'lopp until the upper bound of the array
'If the item in the list box = the item in the array then highlight the item in the list box
If List96.ItemData(i) = lst(j) Then
List96.ItemsSelected(i) = True
End If
Next j
Next i
In a nutshell, that's the problem. My list box contains a list of about 5 or 6 things, the user can select any number of them, and I store it in a single field in the DB semi colon delimited. However, I don't know how to return that field to my list box with the selected values highlighted. IE if the list contains A, B, C, D and the user selected A, C the field in the DB looks like this A;C. When I open the form again, I want to highlight A and C in my list box since those were the fields the user selected.
What happens is A;C gets passed to another application (which wants it that way). However, in that other application those values may or may not change, and then they will be passed back to me. (IE the other application may change those values to A;B;D and pass it back to me (which I accept no problem). However, when the user opens the form I want A B and D to be highlighted in the multi select list box
Any help is much appreciated
Robbo
View 4 Replies
View Related
Oct 10, 2011
2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records.
In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working.
View 5 Replies
View Related
May 19, 2014
I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:
Code:
'UPSData Class Module
Private p_LetterArray() As String
Private p_date() As Date
Private p_LetterArraySize As Integer
My Properties
Code:
'Properties
Public Property Get LetterArray(index As Integer) As String
LetterArray(index) = p_LetterArray(index)
End Property
Public Property Let LetterArray(index As Integer, NewValue As String)
[Code] ....
As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.
When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:
Get LetterArray is called. p_LetterArray(0) does equal "src"
Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger
Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""
Same steps happen with the same results ("12:00:00AM" vice "")
View 5 Replies
View Related
Jun 18, 2013
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user
2) Search that Part Number into Table1
3) Take returned Parent Values associated with Part Number and store in Array
4) Modify Array values
5) Search Array values using a For Each loop into the query
6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query.
7) Also return the total rows in a specific column (Order Numbers)
View 10 Replies
View Related
Jun 27, 2013
I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.
Table 1
EmpID Name
1 EMP1
2 EMP2
Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B
Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18
View 2 Replies
View Related
Jun 14, 2013
I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up
Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.
Lets use group as an example.
When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.
Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.
I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.
The SQL:
SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv,
Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type
FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid
WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')
[Code] ....
View 2 Replies
View Related
Jan 2, 2014
I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.
I have written a query that pulls to fields of data together but cannot get the two to calculate.
Within SQL server management studio I use the following query to give me a numerical response.
SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE
Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?
View 9 Replies
View Related
Sep 13, 2013
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
View 7 Replies
View Related
Mar 3, 2015
i would like to have on my continuous subform fields from my table but only as a non-editable list (I have a dedicated mod form to actually modify the values), and I don't really like the way "Locked" and/or "Enabled" work, especially on combo boxes. To me, the arrow shouldn't be there: it looks like you can modify it, but you can't; it's a bit confusing and counter intuitive.
So what I did is replace those combo boxes with text boxes, and used DLookUp in the Control Source. Now I can lock and disable the text boxes and it looks just as I want it: you can't select the text, and most importantly there's no dropdown arrow.It works, but it's kind of a lot of work compared to what it is with the cbo. Also, since those are calculated values, they take a fraction of a second to appear, as opposed to the nearly instantaneous display of the values with the cbo, and there's only 5 entries in my test database.
View 8 Replies
View Related
Dec 11, 2013
I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long
ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long
ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long
StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours
View 7 Replies
View Related
Nov 18, 2013
I have a list of employees and sort criteria. for example
empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F
The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0
E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0
I can get it to return:
empID......Count
1234...........2
1236...........3
using Count and the criteria Where Criteria="T" but not returning zeros.
View 2 Replies
View Related
Feb 10, 2014
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code:
SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;
Why some contacts work and some don't ??
View 7 Replies
View Related
Sep 30, 2013
I would like to create a command button on my form that copies values from 5 fields in the current record into a new record leaving all the other fields blank except for the new record ID.
The fields that contain the data that I want copied into a new form are:
1. Member_Name
2. Member_ID
3. Account
4. UBH/PBH
5. Assigned_WRCA
View 8 Replies
View Related
Mar 6, 2007
My problem is as follows:
User group 1 enters information into a table via a form.
This information then needs to be signed off by user group 2. I have created a query which shows the information in fields 1 to 10 which I want to be protected so they can only view this and not edit it and then have a couple of fields at the end where they can enter their name and any comments.
Simply my question is can I have a query with some protected fields and some un protected or is it one or the other? If this is not possible please suggest an alternative.
View 2 Replies
View Related
