how to insert new rows to a table in ms access

[mana]

hello

i have a two tables in ms access: "checkpoints" and "checks_projects"
i have a form and there is a date text box in it. i added data to checks_projects table form checkpoints where the creation_date field of the checkpoints is less than this textbox.
but after sometime some new data will be added to the checkpoints tableand i want to add this new daat those are less than this text box date to the check project table. but i don't how to do this
can you help me please?
i wrozte the below code but it doesn't work
thank you

DoCmd.RunSQL ("insert into checks_projects(Creation date) select Creation date from CheckPoints left join checks_projects on CheckPoints.projectname=checks_projects.projectname where checks_projects.projectname=text0.value and checks_projects.Creation date is null;")

 

[arnelgp]

DoCmd.RunSQL ("insert into checks_projects([Creation date]) select [Creation date] from CheckPoints left join checks_projects on CheckPoints.projectname=checks_projects.projectname where checks_projects.projectname= '" & text0.value & "' and IsNull(checks_projects.[Creation date]);"

 

[mana]

thank you for your answer

but it has syntax error in this part:

where checks_projects.projectname= '" & Text0.Value & "' and IsNull(checks_projects.[Creation date];")

 

[arnelgp]

sorry about that:
DoCmd.RunSQL "insert into checks_projects([Creation date]) select [Creation date] from CheckPoints left join checks_projects on CheckPoints.projectname=checks_projects.projectname where checks_projects.projectname= '" & text0.value & "' and IsNull(checks_projects.[Creation date]);"

 

[mana]

thank you
i made it correct
but still i have the same problem

 

[mana]

it has this error this time:

"the specified field creation date could refer to more than one table listed in from clause of your sql statement"

can you help me please?

 

[mana]

hello

i changed it to this code but i have compile error
can you help me please?

DoCmd.RunSQL ("insert into checks_projects([Creation date]) select a.[Creation date] from (select [Creation date] from CheckPoints) as a left join checks_projects b on a.[Creation date]=b.[Creation date] where a.[Creation date]< Replace(Format(Me.Text5, "mm/dd/YYYY"), ".", "/") and b.projectname= '" & Me.Text0.Value & "' and IsNull(b.[Creation date]);")

 

[arnelgp]

DoCmd.RunSQL ("insert into checks_projects([Creation date]) select a.[Creation date] from (select [Creation date] from CheckPoints) as a left join checks_projects As b on a.[Creation date]=b.[Creation date] where a.[Creation date]<#" & CDate(Replace(Me.Text5, ".", "/")) & "# and b.projectname= '" & Me.Text0.Value & "' and IsNull(b.[Creation date]);")