How to make sequential date from non-available date data

[KarenY]

I have a table of which the data is imported from Excel. I receive the Excel
file from my colleague only if she has activity on that day, in other words,
the activity data will append to the existing table.

One of the field in my table is "ChangeDate".
There may be a couple of rows (more than one customer) for one ChangeDate.
All the fields' name of the table:
custcd, changeDate, creditLimitY, creditLimitN, userID

I need to run a query to show day 1 till the last day of the month (i.e. 1st
till 30th or 31st). The format of the "changeDate" is mm/dd/yy. Since the
"changeDate" does not cover all the "dates" of the month in the table, can I
still run a query with the following requirement:
(1) add the no-activity day's "date" in the "changeDate" field;
(2) the words "Nul change" in the "custcd" field.

I have already created 2 extra fields "Month" and "Year" in the query using
the "changeDate" field to pull the relevant month and year.
Reason for running the query, I need to export to Excel. My colleague
doesn't want to have the report but Excel worksheet that he needs to work on.

Please help.
thanks
Karen

 

[KARL DEWEY]

Create a table named CountNumber with field named CountNUM containing numbers
from 0 (zero) through 99.
Use this query substituting your table name --
SELECT DateAdd("d",[CountNUM],Date()-Day(Date())+1) AS Expr1,
IIf([changeDate]=DateAdd("d",[CountNUM],Date()-Day(Date())+1),[custcd],"Nul
change") AS Expr2
FROM CountNumber, KarenY
GROUP BY DateAdd("d",[CountNUM],Date()-Day(Date())+1),
IIf([changeDate]=DateAdd("d",[CountNUM],Date()-Day(Date())+1),[custcd],"Nul
change")
HAVING
(((DateAdd("d",[CountNUM],Date()-Day(Date())+1))<=DateAdd("m",1,Date()-Day(Date())+1)-1))
ORDER BY DateAdd("d",[CountNUM],Date()-Day(Date())+1);

 

[KarenY]

Appreciate your response, Karl.
I am not good in SQL. Yet I did try and follow your insturction.
I am sorry, I must have done something wrong.
It gave me an error msg:

The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuatoin is incorrect.

Then I closed the pop up message, the word HAVING was hi-lighted in the SQL.

As I posted my problem in the New User, is this possible for you to explain
in the normal design view for me to work on ? If not, would you please
elaborate step by step to me, sorry.

thanks
karen

Create a table named CountNumber with field named CountNUM containing numbers
from 0 (zero) through 99.
Use this query substituting your table name --
SELECT DateAdd("d",[CountNUM],Date()-Day(Date())+1) AS Expr1,
IIf([changeDate]=DateAdd("d",[CountNUM],Date()-Day(Date())+1),[custcd],"Nul
change") AS Expr2
FROM CountNumber, KarenY
GROUP BY DateAdd("d",[CountNUM],Date()-Day(Date())+1),
IIf([changeDate]=DateAdd("d",[CountNUM],Date()-Day(Date())+1),[custcd],"Nul
change")
HAVING
(((DateAdd("d",[CountNUM],Date()-Day(Date())+1))<=DateAdd("m",1,Date()-Day(Date())+1)-1))
ORDER BY DateAdd("d",[CountNUM],Date()-Day(Date())+1);

--
KARL DEWEY
Build a little - Test a little

I have a table of which the data is imported from Excel. I receive the Excel
file from my colleague only if she has activity on that day, in other words,
the activity data will append to the existing table.

One of the field in my table is "ChangeDate".
There may be a couple of rows (more than one customer) for one ChangeDate.
All the fields' name of the table:
custcd, changeDate, creditLimitY, creditLimitN, userID

I need to run a query to show day 1 till the last day of the month (i.e. 1st
till 30th or 31st). The format of the "changeDate" is mm/dd/yy. Since the
"changeDate" does not cover all the "dates" of the month in the table, can I
still run a query with the following requirement:
(1) add the no-activity day's "date" in the "changeDate" field;
(2) the words "Nul change" in the "custcd" field.

I have already created 2 extra fields "Month" and "Year" in the query using
the "changeDate" field to pull the relevant month and year.
Reason for running the query, I need to export to Excel. My colleague
doesn't want to have the report but Excel worksheet that he needs to work on.

Please help.
thanks
Karen