I have a combo box as a value list and all the values apart from one are valid data entries in the table but I want to combine two of the values and then get the query to run if the combined values are selected. The value list is this:
(All);Brand1;Brand2;Brand1 & Brand2;Brand3;Brand4
The criteria in my query is this:
IIf([Forms]![MyForm]![Brand]='(All)',[MyTable]![Brand],IIf([Forms]![MyForm]![Brand]='Brand1 & Brand2',"Brand1" Or "Brand2",[Forms]![FrmCustomListATM]![cbBrand]))
Here is what I have in my Query IIf([Forms]![Report Form]![Combo56]=0,1 or 3,[Forms]![Report Form]![Combo56])
in the combo box I have it setup like this Yes = 1 No = 3 All = 0
it is pulling for a SQL database and what I am looking to do is if they want them all select 1 and 3 but when I save the Query is changes it to this and doesn't work.
IIf([Forms]![Report Form]![Combo56]=0,([BigAssQuery MTD].[Calc_ID])=1 Or ([BigAssQuery MTD].[Calc_ID])=3,[Forms]![Report Form]![Combo56])
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I'm an extreme newbie, I do not know access very well, nor do I know VBA, I do know PHP.
I have a for in access that has 2 user input fields, one for prodid one for shipid. I have a combo box that upon entering data into the previous 2 fields, it does a query against an MsSQL database looking for a record that has both. In any case there will only be 2 outcomes, either 1 record, or null. I would like to have that same combo box automatically use the result as it's value so that users don't have to check the box, because they won't, and so that the rest of my VBA will be able to function properly.
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
Whats the best way to show the text result of a combo box selection when displaying the information through a query? I notice the table is also reflecting only the numerical ID result of the selection so I'm not sure how to take this result and display it as a text result to the user.
I'm working on a project that uses a barcode scanner. The user will scan a barcode which will choose an item from a combo box. The result of what it enters will populate a text box. I have that part working fine.
The next step is to get it to open a form based on the value in the textbox. The textbox will have about 7 different possible values and each one should open a different form.
The way this is supposed to work:
User approaches a machine and scans the barcode. He is presented with a form giving him options based on the type of equipment the machine is. A compressor for example would present him with a set of options for compressors (gauge readings, maintanence, etc).
I have an unbound form with two controls:
cboAssetNumber txtType
How to code this and which events to code it in. Users will be going from machine to machine and using the form over and over again so I need to somehow manage the clearing of the form to begin again with another barcode scan.
I saw another thread about a barcode scanner and one response was to program the scanner to send the barcode and then send an "Enter" press to move focus to the next control. I've looked through the documentation and haven't been able to find if that is possible. Right now it sends the number and that's it. It's a "Motion" Tablet.
I have a query, called Building Query. it has 5 columns.
Building EquipmentLocation PartName Category PartNumber
I have created a form, with these five as comboboxes. I want each to reference the one before it, so the user can select the part without error. This is somewhat complicated because each building has multiple locations, which in turn have multiple parts, which can be in mutiple categories, etc............ However, althought the selection process works it's not accurate. example: say I select building 1. and location 1.2. The part name should be limited to "belt" and the category limited to "ef", thus limiting the part number to 5vx630. This is what happens instead: I choose building 1, and then location 1.2. The part name is limited to "belt". The category gives me a full list of all four categories available in building 1. So then I choose the category "ef" (because I know what to choose) and am given the full list of all "EF" part numbers. So It seems to work fine until the last two selections. Perhaps you can help.
Name: cboBuilding RowSource: SELECT Building.BuildingList FROM Building; after update event procedure: Private Sub cboBuilding_AfterUpdate() Me!cboLocation.Requery End Sub
Name: cboLocation Rowsource: SELECT [Building Query].EquipmentLocation FROM [Building Query] Where BuildingList = cboBuilding AfterUpdate Event Procedure: Private Sub cboLocation_AfterUpdate() Me!cboPartName.Requery End Sub
Name: cboPartName RowSource: SELECT [Building Query].[PartName] FROM [Building Query] Where EquipmentLocation = cboLocation AfterUpdate Event Procedure: Private Sub cboPartName_AfterUpdate() Me!cboCategory.Requery End Sub
Name: cboCategory RowSource: SELECT [Building Query].[Categories_CategoryName] FROM [Building Query] Where PartName = cboPartName AfterUpdateEventProcedure: Private Sub cboCategory_AfterUpdate() Me!cboPartNumber.Requery End Sub
Name: cboPartNumber RowSource: SELECT [Building Query].PartNumber FROM [Building Query] Where Categories_CategoryName = cboCategory AfterUpdateEventProcedure: Private Sub cboPartNumber_AfterUpdate() Me!Productssubform.Requery End Sub (this is to requery the subform the then displays the part number, the number in stock, and the number needed to be at full changeout level) This also isn't working all of a sudden
Can anyone help me???? It must be simple but I've read every thread in here about combo boxes and SQL's and just can't figure it out myself.
I'm trying to get an SQL statement to take a value from a combo box in a WHERE LIKE clause.
For example:
INSERT INTO tblInspectionTempp (BuildingID, DoorNumber) SELECT tblDoorData.BuildingID, tblDoorData.DoorNumber FROM tblDoorData WHERE tblDoorData.BuildingID LIKE = '[Forms]![fmInspectionColumns]![cmboBuildingID].Value'"
The errors I'm receiving are either Missing Match or incorrect Syntax, depending on my trial and errors methods regarding the WHERE clause.
I have created the following function in my datebase so that i could specify the date a query works from.
Option Compare Database
Function GetParmValue() As Date
GetParmValue = InputBox("Please enter the date you wish to update!?")
End Function
I have a button on a form that runs the query in question, there is also an identical query that needs to be ran using the same result from the input box but rather than it display the input box twice i wondered if there was a way i could use the result from one of the boxes with both queries!?
Here is the code for the button;
Private Sub bImport5_Click()
DoCmd.SetWarnings False DoCmd.OpenQuery "Update" DoCmd.OpenQuery "UpdateFuture" DoCmd.OpenQuery "MoveFuture" DoCmd.SetWarnings True MsgBox "Files have now been updated to the specified date and moved to the Future Dated table!", vbOKOnly, "Update Complete"
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
I have a query which i need to get Zero as a result if NULL value. How Can i do this ? Present SQL code is as follows;
SELECT tbl_Impts_main.Bkg_number, Count(tbl_Impts_main.Bkg_number) AS CountOfBkg_number FROM tbl_Impts_main INNER JOIN tbl_booking ON (tbl_Impts_main.Voyage=tbl_booking.Voyage) AND (tbl_Impts_main.Vessel=tbl_booking.Vessel) AND (tbl_Impts_main.Bkg_number=tbl_booking.Bkg_number) GROUP BY tbl_Impts_main.Bkg_number HAVING (((tbl_Impts_main.Bkg_number)=[forms]![frm_Export_data_entry]![bkg_number]));
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
