Search results

I'm sorry I misunderstood your question. I think the nested IIF in your original query has made Access treat the two parameters as text strings rather than two dates. Your can try declaring their data type. In query Design View, choose menu Query, Parameters... In the dialog box that pops...

The between dates parameter should apply to the DateDone field, not the nested IIF() expression for TestDueNext. Note: In an IIF() expression, besides dealing with the situation when the condition is true, we also need to deal with the situation when the condition is untrue. So the...

See if this is the query that you wanted. SELECT ID, Company, Month, [PPM Month], [Grant Shipped], [Grant Rejected], Round(IIf([Grant Shipped]=0,0,[Grant Rejected]/[Grant Shipped])*1000000,4) AS [Monthly Grant PPM], [Hart Shipped], [Hart Rejected], Round(IIf([Hart Shipped]=0,0,[Hart...

It's unclear what you want your query to delete. In a delete query, you can delete records from ONE table only, not from more than one table. The follwoing two queries will work, though they may not be exactly what you wanted. DELETE DISTINCTROW tbl_Table1.* FROM tbl_ExcludeCLI LEFT JOIN...

In hotel-room booking where the BookingEndDate of a room can be the BookingStartDate of a new booking, you can use < and > rather than <= and >=. .

Try a query like the following: SELECT * FROM [TableName] WHERE [EndDate]>=[Enter range start] and [StartDate]<=[Enter range end] .

You can first build a Totals Query to Group By name and Max date. Then in a second query, join the first query back to the table to retrieve the fields. qryOne: SELECT sample.Name, Max(sample.Date) AS MaxOfDate FROM sample GROUP BY sample.Name; qryTwo: SELECT sample.ID, sample.Name...

I have attached a sample database, which contains a modified Orders table from the Northwind sample. You can open the form, select a month from the combo box and click on the command button to run the query. Note the Row Source property of the combo box on the form and the criteria in the...

Try this criteria in a NEW column in the query grid in query Design View:- ------------------------------------------- Field: IIf([Forms]![Form1]![cboDate]=1; [tblITEM]![date_ID] is not null; IIf([Forms]![Form1]![cboDate]=2; [tblITEM]![date_ID] is null; True)) Show: uncheck Criteria: True...

Try this query. SELECT Trainer, [Todays Date]-Weekday([Todays Date],2)+7 AS WeekendingDate, Round(Sum([Finish Time]-[Start Time]-#0:30:0#)*24,4) AS WeeklyHours, Int(Sum([Finish Time]-[Start Time]-#0:30:0#)*24) & " hr " & Format(Sum([Finish Time]-[Start Time]-#0:30:0#),"nn"" min ""ss""...

Try something like the following: Switch([Num]=24,624, [Num]=26,626, [Num]=28,628, 111,999) +0 111 - an unlikely number in [Num] 999 - a default value +0 - converts returned values to numeric .

Try this: currDate = Format(Date, "mm/dd/yyyy") .

Instead of using Distinct in qryOne, you can change it to a Totals Query and use Sum to return the total numbers in the query: New qryOne:- SELECT [Name], [Letter], Sum([Number]) AS SumOfNumber FROM [Test] GROUP BY [Name], [Letter] .

SQL statement in the query:- INSERT INTO tblNames (Name) values ([Forms]![yourFormName]!textbox1) Code in the ON Click event of the command button:- DoCmd.OpenQuery "yourQueryName" Note: Name is a reserved word in Access. Better avoid using it as a field name. .

You can join your query to my qryTwo in one new query. SELECT yourQuery.Name, yourQuery.Count, yourQuery.AvgOfNumber, qryTwo.Count as CountOfLetter FROM yourQuery INNER JOIN qryTwo ON yourQuery.Name = qryTwo.Name; .

Do can do it in two queries. qryOne:- SELECT DISTINCT [Name], [Letter] FROM [TableName] qryTwo:- SELECT [Name], Count("*") AS [Count] FROM qryOne GROUP BY [Name] Run the second query. .

The expression that I gave you works in a query. To use it on a form, you can simply replace [Date] with the text box name. See the database. .

Group: Year([Date])-(Month([Date])>3) Note: Date and Group are reserved words. Better avoid using them as field names. .

Can you post your database with some sample data? Preferably in Access 2000 format. .

Type this criteria in a new column in the query grid (replacing with the correct field name, form name and text box name):- ------------------------------------ Field: IIf([Forms]![FormName]![TextBoxName] Is Not Null, [FieldName]=[Forms]![FormName]![TextBoxName], [FieldName] Is Null) Show...