Append Query Problems 1

[RobNauticus]

Hello all nice to see you again,

I am having a problem creating an append query to populate multiple records from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit button that runs the query. The query will take that date and create a record for each task in a list (separate table).

I created an expression that points to the field on the form to append to the date field in the completed tasks table. Whenever I attempt to run the query it gives me a type conversion failure. The field on the form is a date field and the field on the table is a date field as well. When I go to preview the query the field header shows Expr1 and the fields have some blank characters in each.

Any leads on where to check for problems would be appreciated.

Thanks
Rob

 

[PHV]

Without seeing your code it's hard to say anything ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

SELECT CompletionLog.[JDE Division Code], CompletionLog.[JDE Number], CompletionLog.TaskName, CompletionLog.DateComplete, JobTable.[Master Plan], JobTable.Community, CompanyTable.[Company Name], JobTable.[# Homesites]
FROM MasterTaskList INNER JOIN ((CompanyTable INNER JOIN CompletionLog ON CompanyTable.[JDE Division Code] = CompletionLog.[JDE Division Code]) INNER JOIN JobTable ON (CompletionLog.[JDE Number] = JobTable.[JDE Number]) AND (CompanyTable.[JDE Division Code] = JobTable.[JDE Division Code])) ON MasterTaskList.ID = CompletionLog.TaskName
WHERE (((CompletionLog.DateComplete) Between [Enter Start Date] And [Enter End Date]));

Here is the SQL. I will post the query grid next.

Thanks

 

[PHV]

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

INSERT INTO CompletionLog ( [JDE Division Code], [JDE Number], TaskName, DateComplete )
SELECT CompletionLog.[JDE Division Code], CompletionLog.[JDE Number], MasterTaskList.TaskName, [Forms]![NewJobSetup]![DateCommitted] AS Expr1
FROM MasterTaskList, JobTable INNER JOIN CompletionLog ON (JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE Division Code] = CompletionLog.[JDE Division Code])
WHERE (((CompletionLog.[JDE Division Code])=[Forms]![NewJobSetup]![JDE Division Code]) AND ((CompletionLog.[JDE Number])=[Forms]![NewJobSetup]![JDE Number]))
ORDER BY CompletionLog.[JDE Division Code], CompletionLog.[JDE Number], MasterTaskList.TaskName;

Sorry wrong one...Please check again

 

[PHV]

Have you tried this ?
PARAMETERS [Forms]![NewJobSetup]![DateCommitted] DateTime;
INSERT ...

For me, an ORDER BY clause is irrelevant in an append query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

You are awesome, that was it!

Thanks...You would think that MS would have that added in for me. Oh well thanks again.

Rob

 

[RobNauticus]

Well it still gives me the error I reported before.

The dates fill in properly but the query still wont run.

Rob

 

[PHV]

You may have to define the others parameters too ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

Ok I got all the parameters in there and correctly defined. Here is where I stand...

PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName, DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1, [Forms]![NewJobSetup]![JDE Division Code] AS Expr2, [MasterTaskList]![ID] AS Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON (JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE Division Code] = CompletionLog.[JDE Division Code])) ON MasterTaskList.ID = CompletionLog.TaskName;

There is something still wrong that I cannot figure out. Now I get zero records to append. Any more suggestions?

Thanks again,
Rob