Search results
-
E
Select records in Table 1 that are not in Table 2
I think instead of criticizing, you should have apologized at least to Jon K for having posted misleading table structures and data and for having wasted his and other members' time. Jon K has posted a query that works perfectly on the table structures and data given in your first post. And... -
E
Select records in Table 1 that are not in Table 2
The sample data given in your first post do not include a numeric field of FK_StatusID for the indications of Accepted and Not Accepted. Besides, a Left Join with Is Null is more efficient than Not In A Subquery. If your table is fairly large, you can immediately notice the difference. ^ -
E
3 Parameter Query For A Subfrom
Try Jon K's basic criteria format as described in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^ -
E
Best 5 Day Average
The question is a little vague. Different understanding of it may lead to different solutions. Do you mean you want the query results to show the top 5 records based on productions in a specified period time and at the same time show the average production of these 5 records? ^ -
E
Parameters determined by combo boxs on forms
See Jon K's basic criteria format and his Note (2) in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^ -
E
RunSQL for Update
You need to have a space between [Admin Leffer App/Decl] and the word WHERE. I would put spaces between continuous lines at the beginnings. SQL = "UPDATE tblSoldCase" & _ " SET tblSoldCase.[Case Name] = tblProspect.[Case Name]," & _ " tblSoldCase.[Admin Letter App/Decl] =... -
E
Goto a date
Glad it works for you. The kudos goes to Jon K who, without info about your table structure, had to stab in the dark. CDate() is a function that converts a string to date/time data type. In the line - rs.FindFirst "[Date] >= CDate('" & Now & "')" though Now() returns date/time data type, the... -
E
Goto a date
Try CDate and Now. They include date and time. rs.FindFirst "[Date] >= CDate('" & Now & "')" If it still doesn't work, try using another name for the date/time field because Date is a function name. ^ -
E
Mean Time Between Failure
Though the title of the thread is Mean Time Between Failure, it seems what the OP wants is the number of days between the previous close date and the open date. In the example, 5 (days) is given as between 1/5/2007 and 1/10/2007. ^ -
E
Query iff help
Try Jon's basic criteria format as described in his thread: http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^ -
E
grouping two tables which have different number of data
In query Design View, double click on the line joining the EmpName fields in the two tables. In the dialog that pops up, select the option that includes all records from Table A. Click OK. Or write in SQL View, SELECT [Table A].EmpID, [Table A].EmpName, [Table B].EmpHobby FROM [Table A] LEFT... -
E
searching on multiple criteria problem
See Jon K's basic criteria format in this thread and his Note (2) about issues of using the Like operator:- http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^ -
E
Error checking and multi field searching
To dynamically build an SQL string in VBA, see this link:- http://support.microsoft.com/kb/210242/en-us Alternatively, you can use a query. See Jon K's sample and his Note (2) in this thread:- http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^ -
E
Query Criteria
Glad it works for you. And all kudos goes to Jon K, who developed the original queries. ^ -
E
Query Criteria
Without seeing your table structure and data and queries, it's not easy to guess where the problem would lie. Did you have that same error in my database? I have no problem running the database in Access 2K and 2003 even with as type added at the end. As far as I know, some versions of Access... -
E
Query Criteria
See the database, which treats 'debit memo' as if it is 'invoice'. To retain Jon's queries as much as possible, I just renamed Query1 to Query0 and added a new Query1 based on Query0. And I added 'debit memo' in the query 'qryInvoices only'. The logic: Jon used a Totals Query (Query1) to... -
E
Using Forms!FormName!Control Is Null in criteria
Jon K addressed this issue and posted such a solution in the Samples forum back in March 2006. He used what he called the Basic Criteria Format in a new column in query design: ---------------------------------------- Field: [FieldName]=Forms!FormName!Control OR Forms!FormName!Control Is Null... -
E
Query Criteria
You can link Jon's Totals Queries in new queries to extract records from the table. See my three new queries in the attached database. ^ -
E
Query Criteria
I have been watching this thread. From the start, it was about retrieving unique lists of account numbers, so it was a little obscured that what you wanted was all the account numbers including duplicates for mixed. Anyhow, now that you have a list of unique account numbers for mixed in Query2... -
E
I can't get this to work IIf function work for me
Again change the Or to + Other Qty: Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=95 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],0))+Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=100 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],0)) + Sum(IIf([QSum]![ACCOUNT]=3 And [QSum]![ICUNIT] In (2,100)...