Recent content by ldandy

Access 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...

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)