runSQL

[Miranda]

hi all,

I want to do this insert statement, but i get a mysterious error message
that sheds no light on what the problem is. I'm assuming it's the nested
select part.....The value of costPerHour should already be in the Equipment
Assigned Table...in theory it won;t ever be null.


INSERT INTO [Equipment Assigned] (equipType, jobDesc, costPerHour)
VALUES (FORMS!AddProjectDetails!assignedEquip,
FORMS!AddProjectDetails!project,
(SELECT costPerHour
FROM [Equipment Assigned]
WHERE jobDesc = FORMS!AddProjectDetails!project));

 

[Steve Schapel]

Miranda,

No, the syntax of the SQL doesn't work. The nested SELECT statement
could theoretically return multiple records, and you would still need to
indicate which record you need the value from. It is not really clear
what you are trying to achieve. It is conceivable that this will do
what you want...

INSERT INTO [Equipment Assigned] (equipType, jobDesc, costPerHour)
VALUES (FORMS!AddProjectDetails!assignedEquip,
FORMS!AddProjectDetails!project, DLookup("[costPerHour]","[Equipment
Assigned]","[jobDesc] = FORMS!AddProjectDetails!project"))

.... which would be the same as, and better put as...

INSERT INTO [Equipment Assigned] (equipType, jobDesc, costPerHour)
SELECT Forms!AddProjectDetails!assignedEquip, jobDesc, costPerHour
FROM [Equipment Assigned]
WHERE (jobDesc = FORMS!AddProjectDetails!project)

.... but this assumes there will already be a record in Equipment
Assigned with the same jobDesc as the record you are trying to add, - is
this what you want? And then if there are in fact already more than one
record with the same jobDesc, it will use the costPerHour of the first
one it finds - is that what you want? Please post back with more
details if you need more help.