I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.
I wrote that:
Code:
SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound
FROM MyTable
WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1])
Between 1/1/2013 And 31/12/2013)
The query above extra extract some records without sense for me, I tried many other syntax without success.
Is there a way to select a query field based on conditions? There is a table at work that I need to use for my reporting in which the field names are the months of the year. Since my report is monthly for that specific month only, I have to change my field in the query every single month. I would like to be able to put in some type of code that will automatically select the proper month field.
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
I have the following SQL statement. The COUNT(b.BookingID) AS TB, gives me the number of bookings. I need to ensure that the number of bookings + PrebookCapacity is less than v.Capacity. I do this by:
COUNT(b.BookingID) AS TB + PrebookCapacity < v.Capacity in the WHERE CLAUSE. But when I run this in MS Access 2003, it says Enter Parameter Value for b.TB (It does not recognise b.TB as b.TB is not a table field). If I omit AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)), from the WHERE clause, it works fine, also giving me the TB.
As I need to do my check in the WHERE clause, how can I check to make sure that the COUNT(b.BookingID) AS TB + PrebookCapacity is < v.Capacity, without any problems.
SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity, COUNT(b.BookingID) AS TB FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)) GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;
I am working on a commercial website that is using ASP and an access database.
I have a pretty elaborate select statement that works, except for part of the ORDER clause.
Here is the scenario.... my client sells automotive parts...
Table 1: (itemsku) holds part numbers Table 2: (itemveh) holds vehicles that work with those parts along with some descriptive clarification (cab size, bed size, years, ...) Table 3: (make) holds the vehicles make name (ford, chevy, ...) Table 4: (model) holds the vehicles model name (F150, Ram, Silverado...)
I need to get a list of parts and their approriate descriptions and sort it by Make, Model, Specific Description, and year.
((itemsku left join itemveh on itemsku.id=itemveh.itemsku_id)
left join model on itemveh.model_id = model.id)
left join make on model.make_id=make.id
where itemsku.item_id=4
order by
make.name,model.name,itemsku.desc_sku
If I put it as listed, all sorts properly, except for the years. As soon as I add to the end of the order by....
, itemsku.yr_beg desc
Then, the desc_sku portion does NOT sort properly, but the years do. I have tried dropping the desc as well to see if that would help, but the desc_sku does not play into the sort factor if the yr_beg is added.
Am I missing something or is their a limit as to the number of fields in an ORDER?
I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS FROM qryFirst GROUP BY qryFirst.PROBLEM_ID;
However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I get an error „You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)“
I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!
I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.
For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.
To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.
It was simple enough to do this one column at a time...
UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"
WHERE ((([Capture history intermediate].[1971]) Is Null))
But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.
So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately?
Is it possible to have a form designate which FIELDS to include in a selection query? I know that I can determine criteria and such, but i want the user to be able to generate a report based on the query select a, b, c from blah, or if they wish, just select a, b from blah...
I want to create form with 10 checkbox, each checkbox is linked to a field "name, Address, TelephoneNumber, BuissinessName" I would like to know if it is possible for the user to check the box and then the field will show in query. if only name is check then only name will show in the query.
I have tried using [Forms]![Formname]![CheckboxName] as criteria but this didn't work it when I had more then one checkbox and when the one check box was not checked nothing showed in the forms.
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
UMMonth1: Choose([Enter Qtr],[OperationsAuditData]![1],[OperationsAuditData]![4],[OperationsAuditData]![7],[OperationsAuditData]![10]).But I keep getting an error message "You tried to execute a query that does not include
I have an option group that lists a variety of categories which I want to query on.
I know that the option group only returns values.
In the Option Group, On Time = 1, Over Due =2 ,,,,
I am using a Choose function in my query. That is working fine. But ... I would like to have a last choice called "All" such that when selected all data is shown (i.e., On Time, Over Due, ....).
I am using the following:
Choose ([optiongrpStatus],"On Time", "Over Due", .... ,???) as my query criteria.
Have not figured what I can put in the last choice to get all data.
Tried "Is Not Null", tried "On Time or Over Due" - know these don't make sense -just wanted to try.
I have a form with 2 cbo. I want query to show all data if nothing has been selected, or show specific data to whatever has been selected in cbo1 AND/OR cbo2.My query was working ok for couple of days and then stopped. I don't know why, as no changes has been made.
if both cbo are blank then it works OR if both cbo have a selected value then it works, ok. But it doesn't if one of the cbo has a value and the other one hasn't.
Code: WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea AND dShiftFK=Forms!F_MainMenu.cboStatsShift) OR (Forms!F_MainMenu!cboStatsArea IS NULL AND Forms!F_MainMenu!cboStatsShift IS NULL)
I have several queries (crosstabs) which are eventually combined together to form the data for a single report. If I want to date select for this report I need to add a WHERE clause to every query via VBA. This isn't difficult but it set me wondering if there was any way to have one dummy query containing the necessary WHERE statement, and this somehow gets appended to all the other queries, i.e. to filter on date I only need a change in one place.
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
I have a combo box on a form where I allow the user to pick an office. The primary key is the bound column which I then want to use in a stored query to limit the query results to the specific office selected. I can easily do this with the docmd.runSQL in the module, but I'd like to use a stored query instead. My exact problem is I can't get the syntax correct in the Where clause in the query design. I use the build feature and get the following: [Forms]![frmReports]![cboOffice] which doesn't work. If I manually put the key value in the Where clause, it works fine. tell me the correct syntax for the Where clause.
From a performance perspective, does it matter in what order a number of clauses are specified ? For example if many records satisfy ConditionA but few records satisfy ConditionB, is it better to put ConditionB first ?
SELECT Fields FROM Table WHERE ConditionA and ConditionB or SELECT Fields FROM Table WHERE ConditionB and ConditionA
Is there anyway to search each row within one column for a value ? The "IN" clause appears not to work for multiple values in a row.
SELECT Table_Payee_List.CategoryIDs, Table_Payee_List.PayeeSelectBox INTO GOTIT FROM Table_Payee_List WHERE (((Table_Payee_List.CategoryIDs) In ("25")));
Data (two columns with 5 rows) are represented below:
Payee CategoryIDs Column Column
John Doe 21, 27, 32, 34, 44 - Row 1 Jane Doe 20, 25, 28, 42, 44 - Row 3 Paul Doe 19, 25, 28, 42, 44 - Row 3 James Doe 56, 29, 31, 62, 54 - Row 4 Amy Doe 24, 25, 29, 42, 74 - Row 5
I need to change the below to a where clause to fit inside a union query that is just where clauses.
tblNEWNONTODATA.DateOfVisit) AS FirstOfDateOfVisit FROM tblNEWNONTODATA GROUP BY tblNEWNONTODATA.EVX, tblNEWNONTODATA.TCGDecision HAVING (((tblNEWNONTODATA.TCGDecision)="Adopted" Or (tblNEWNONTODATA.TCGDecision)="Hot Tasked") AND ((First(tblNEWNONTODATA.DateOfVisit))>=#10/1/2014#));
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
I have a SQL query to gather data from a number of tables (balances, accounts, currencies)
Quote:
SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID]) WHERE BalanceDate = #12/10/2013#
How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)
Along the lines of :
Quote:
WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000
I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
