I set my query criteria to my [Forms]![Form1]![Combo4].
After I key a criteria into combo4 , then I open again the query table ,
It did not show up any result , why ?
No combo4 don't have any event.
ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00
This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs
Like Below :
ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00
IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00
I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...
I have one table in my access, and out of that I need to show a queri that will show only following result in 2 columns ID (same as original table)
SCHOOL_YEAR (all the students that are):
1 year and 1 year (repeat) as 1 year
2 year and 1 year (repeat) as 2 year
3 year and 1 year (repeat) as 3 year
Not to show dripouts, na and finished
Original table:
IDNAME
11 year
42 year
53 year
10dropout
11finished
12n/anot categorized
131 year (repeat)
142 year (repeat)
153 year (repeat)
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I have attached two example for your reference.
I am trying to add up a list of dates that match a criteria... a search box result.
I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)
But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.
Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant
[Code] .....
I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?
Is it possible in access 2010 to create a a query that only shows a text box if a combo box criteria is met?
Example: On a form there is a combobox (Result) that can either be negative or positive. If the value is Negative then a query is already setup that populates a mailmerge with some text. If Results=Positive can a query be created that will show the textbox (Data). It only needs to show if the positive criteria is met.
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]))
Its the OR part that doesnt work.
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#dd/mm/yyyy#"
[Code] .....
I'm working on a published sharepoint web access database, writing a criteria expression in data source of a combo box.The field I'm setting criteria is called SharePointAuthor.
I wrote this critea:
IIf(CurrentWebUser(1)="John Doe","*",CurrentWebUser(1))
Trying to show all records when currentwebuser is John Doe, otherwise, show only records created by currentwebuser.Funny thing is that it turns out "John Doe" couldn't see any records, while other users can see their own records as expected.
I have two queries. The unique key in both queries is GUID for katalogposition.
One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.
The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.
The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.
1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".
I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.
How can I do that? Is there another way to do it? Please see attachment.
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 there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedLet me see if I can explain this.
I have two tables: TblValues and TblLOC.
TblValues is a table of stactic values where I have listed about nine field names with values under each that usually will not change. This way if there is a change to one of the fields later, I can just add it to the table.
Then there is tblLOC. Here I have similar field names that matches each field in tblValues where I can populate them in my form. I have several fields defined as combo boxes. In the properties for each field defined as a combo box, for row source type has Table/Query selected and for the row source I have the following code in the table, which is an example for the field named Profit Code in TblLOC.
SELECT tblValues.ID, tblValues.Pro1Code
FROM tblValues
WHERE (((tblValues.Pro1Code) Is Not Null));
Then I have my form where I have referenced my fields from the tblLOC. In the properties of each combo box, under the tab labeled DATA, the control source has the name of the the combo box field that is in tblLOC, in this case Profit Code.
Everything works fine. The user selects a value from the form and tblLOC is updated. I can create queries without criteria with no problem. However, in a query if I try to add any criteria, even as simple as using "like" to identify a value in a field of the table that has been populated by the combo box, I will get no results. However, if I use the same functionality in a column where a combo box is not used to populate the field, I get the results that I expect.
Here is an example of the SQL in the query that does not work for me that is applied against a field that is a combo box.
SELECT tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"));
If I use the same functionality with a field that is not populated by one of the combo boxes then everything works fine.
I am hoping that there's a simple solution for this, maybe syntax due to the use of a combo box or maybe a property that I need to set... So far I have been unsuccessful in finding the answer. If anyone has any ideas on this one, I would certainly appreciate it. Thanks :)
I have a customers list that i am always ading to .I need to run a query to see what individual customers have ordered .
I have the query that works great when i manually insert the customers Surname .but i want the combo to do this job .Is it possible ,or should i be doing something different .
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])
If I select the year from the combo, it works, if I select "ALL" nothing is shown.
here is the full SQL
SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth
[Code] .....
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
View 2 Replies View RelatedOn I form I have a combo box called cboMobileStatus.Its row source type is "Value List".The row source is: 1;" All";2;"Has";3;"None".I have a query based on table "tblCustomerContacts" and I want to limit the records returned based on the value of the combo box.So if the user selects:
* All (1) I want all records returned.
* Has (2) I only want records that have a mobile number returned
* None (3) I only want records that do not have a mobile number returned
The field mobile is text (to keep the leading 0)I thought of using the Switch function in the criteria of the query for field Mobile like this:
Code:
Switch([Forms]![frmPrintCustomer]![cboMobileStatus]=1,([tblCustomerContacts].[Mobile]) Like "*",[Forms]![frmPrintCustomer]![cboMobileStatus]=2,([tblCustomerContacts].[Mobile]) Is Not Null,[Forms]![frmPrintCustomer]![cboMobileStatus]=3,([tblCustomerContacts].[Mobile]) Is Null)
but the query does not like it!Am I on the right track, and if so, how should I modify the code?
Does anyone know if it's possible to show the results of a query on a form? I have 4 people entering various "outcomes" into a table. There are a possible 8 different outcomes and the one i'm interested in is the "completed" outcome. I would like to be able to show on the form used to enter the data, the total number of "completes" so far.
View 3 Replies View RelatedI have a form with combo boxes, each combo box further limits the criteria of a query as selections are made. I have a Me.Requery code for each combo box. - This is working great.
Once all of the selections are complete, I need the result of that query to show in a text box. - There will always be only one result.
I am trying to make a form where the user can check/uncheck query criteria via several check boxes. The idea is that the user can start with many criteria and then deselect criteria if the search does not return enough results.
I have been setting up several queries and thought I would combine them in a "Master Query", since I thought it may be easier to deal with each criterion and the respective switch this way.
Lets say we run a hairdresser.
I have a field in the form that allows me to select clients. This is also used in the query. Works fine. Now to the hard part.
Example 1:
Each customer has a budget to spend on haircuts.
Each hairdresser offers haircuts from $x to $y.
The query should return all hairdressers that are appropriate for the budget of the selected customer.
There should be a yes/no button on the form to ignore or use this criterion.
Example 2 (this completely threw me off):
Each customer has a set of preferred services from a table (e.g. cutting,washing, coloring).
Each hairdresser offers certain services also based on this table (e.g. cutting,washing, coloring, drying).
The customer and hairdresser table use the services table and a multi combo box to select the services.
The query should return only hairdressers that offer some or none of the services wanted by the client.
Again, there should be a services yes/no button on the form to either ignore or include this criterion.
To clarify, the hard part for me is the query. I am fine with setting up the tables and the form. Just not sure how to implement something like "IF (ServicesCheckBox = -1, 'then use service criterion', 'ignore service criterion')".
I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.
I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.
Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.
The code I'm using in each criteria box is;
Like "*" & [Forms]![MyForm]![Combo1] & "*"
and this works as a single criteria in a single field.
I have a user form with six different dropdown boxes. I would like to create a query that gets its criteria from users selecting values from one or more of these boxes.
I have tried:
[forms]![frmName]![comboboxname] or [forms]![formName]![comboboxname] is null
in the corresponding query fields and get a "query is too complex" error message, even when only selecting one criteria.
What am I doing wrong?
Ok so im working in MS Access 2007.
I want to create a query based on 3 combo boxes but have it so that if the field in the second or third combo box is not populated the query still runs.
Right now i have the Criteria set for the three columns that i wish to sort by as seen below.
Column 1
Criteria: [Forms]![Entity Selection Form]![areabox2]
Column 2
Criteria: [Forms]![Entity Selection Form]![devbox2]
Column 3
Criteria: [Forms]![Entity Selection Form]![entitybox2]
This gives me the correct query result but im forced to make a selection from each combo box. Is there a way to progamme it so that if I only make a selection from the first combobox and leave the others blank i can still get results in a query?
Here's the deal:
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.
Can anyone assist?
Thanks.
Hi Experts,
Novice here,
I managed to create a FORM that has fields that calculates basic additions/subtractions formula but for some reason the fields on the TABLE does not update?? Any suggestions?
Thank you,
jola
:confused:
I am writing an Excel VBA code to connect to an Access db and execute an access query. After executing the query, I would like to display the result in the excel spreadsheet.
I was successfully able to make the connection to the access db using the following code:
Set db = OpenDatabase("file name", False, True, "MS Access;PWD=abcd")
However, I do not know what code to use to execute a query named "qrytest" on the Access db and show the result in this Excel file.
Can anyone teach me how to do this please?
P.S. I searched this forum for about 1/2 hour before posting this. Please excuse me if this was addressed anywhere before! Would appreciate a link, if it was.