Search results for query: *

...does fill the records that are not matched with NULL, however in your WHERE clause you have the following condition: ((tblStudentsClasses.strClassID) Like "*" & [Forms]![frmToDo]![txtCurrentSchoolyear] & "*")). This condition eliminates all NULL records. This is the problem.

Hi, The function Now() returns both date and time, and I guess this is the problem. You should try Date() instead. Danny.

Format(YourDate, 'mmmm')

Your define section is the problem. You should change it as follows: #define disc_1 0.08 #define disc_2 0.10 #define Limit 100 If you say: #define disc_1 = 0.08; then wherever disc_1 is met by the compiler, it is replaced with = 0.08; which you don't really want. Danny.

You could use the IIF function for one or more columns in your query, like this: IIF(TABLE_A_COLUMN IS NULL, TABLE_B_COLUMN, TABLE_A_COLUMN) AS YourCombinedColumn

Can you change the date format of your table? I don't know why it doesn't work with the date format you have.

Aietoe, I have created a table with two columns and used the query below: SELECT [TABLE 00IF].Cust, [TABLE 00IF].Prod, DCount('Prod','Table 00IF',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= #&quot; & [Prod] & &quot;#&quot;) AS ProdPos FROM [TABLE 00IF]; I got the following results...

It's strange that Prod field is a date. I thought it was the product number. A date field should be passed to the WHERE clause surrounded by '#'. So the DCount will be: DCount('Prod','Table 00IF',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= #&quot; & [Prod] & &quot;#&quot;) One more...

One problem could be if either Cust or Prod fields are not numeric, then the condition for DCount should be changed a little bit. Another reason of the error could be that either Cust or Prod are null. But since Cust is '0001' in your example, I might suppose it is a text. If that's the case...

When you made the crosstab query, I guess you choose as pivot the Prod column and then you grouped by Cust. This way you will end up with a table that will have as many columns as the number of distinct values in the Prod column plus the columns you have in your SELECT query. The problem would...

Hi Dave, Your query is quite complex and not so easy to follow, however I have a few suggestions. One is to move all conditions from the FROM clause that are not related to table joining (links) to the WHERE clause. And the other: if you say it works for elimline=3 and also for elimline=4 but...

Try WHERE Year(ConsultDate) >= 2002

Rick, I was pointing only where the syntax error come from. Of course your second approach is faster and &quot;cleaner&quot;. Danny.

If your field is called PAYMENT_PAID in the criteria you should say: NOT PAYMENT_PAID.

A solution could be the following: SELECT TableX.COD, TableX.NAME FROM TableX WHERE TableX.NAME = (SELECT TOP 1 TableX.NAME FROM TableX AS TableX_1 WHERE TableX_1.NAME = TableX.NAME)

Does the query below solve the problem? SELECT PARENT.BASEID, First(PARENT.COST) AS ParentCost, Sum(CHILD.COST) AS ChildCost FROM PARENT INNER JOIN CHILD ON PARENT.BASEID = CHILD.BASEID GROUP BY PARENT.BASEID HAVING First(PARENT.COST) <> Sum(CHILD.COST)

You should put all tables and inner joins (together with the ON clauses) in the FROM clause (which will be only one) and all conditions in a single WHERE clause (and link them with AND).

You cannot use IIF in SQL Server, but you can use the CASE statement.

Terry, I'm using Access '97. Probably this is the reason those queries don't for work in my Access. However in SQL Server I'm also using them. Thanks for your answer. Danny.

In Access you cannot use an explicit subquery in the FROM clause, so you cannot say FROM (SELECT ... ). You have to provide a table name or a query name (view) in the FROM clause. You can transform Terry's query in the following way: make the SELECT from the FROM clause a new query and use it in...