The specified field could refer to more than one table in your from clause

[trey3143]

i have a database that keeps giving me the error The specified field [Tester_Info.TNR CC_Reg_Date] could refer to more than one table in your from clause. I have tried everything and can't figure it out. I have a command that runs macro and a query that creates generates the data in the form here is some of my code PLEASE HELP.
Macro:

Case 2
stLinkCriteria = "([CalExpires]>Now()) And ([Tester_Info.TNRCC_Reg_Date]>Now())"
stLabel = "Current Backflow Tester Information"
iColor = 32768

Here is the query:

SELECT Tester_Info.TesterID,
       ([LastName] & ", " & [FirstName]) AS Tester,
       Tester_Info.TNRCC_Reg_Date AS State,
       Calibration_Dates.Calibration_Date,
       ([Calibration_Dates]![Calibration_Date]+365) AS [Good Until]

FROM   Tester_Info INNER JOIN Calibration_Dates
  ON   Tester_Info.TesterID = Calibration_Dates.TesterID

WHERE  (((Tester_Info.TNRCC_Reg_Date)>=Now())
  AND   ((Calibration_Dates.Calibration_Date)<=Now())
  AND   ((([Calibration_Dates]![Calibration_Date]+365))>=Now())

Read more: ** Edit ** Illegal link to alternative forum removed as per site rules.

[nico5038]

I would advise you to transform the Macro's into a module with code (Access offers this option).
Than paste the code here.

Nic;o)

[NeoPa]

We're happy to look at your question for you, but we expect the whole question to be posted here - not to have to look elsewhere to find it.

I'm sure this was simply down to inexperience, but a quick look through our rules is suggested to avoid the possibility of inadvertently breaking them again. They can be found by clicking Help at the top right of every page of the site.

Welcome to Bytes!

-Administrator.

[trey3143]

I transformed the macros into modules but I am a novice and unsure how to use the new modules to get rid of the error that still occurs. Also sorry for breaking forum rules

[nico5038]

No excuses needed, we're here to help and the rules are for making this task easier.

Can you copy/paste the module code here ? That way we can have a look.

Nic;o)

[trey3143]

When I converted Macro in a module of the main form this is what it gave me

Option Compare Database

'------------------------------------------------------------
' Mainform_Close
'
'------------------------------------------------------------
Function Mainform_Close()
On Error GoTo Mainform_Close_Err

    DoCmd.Close acForm, "Main"


Mainform_Close_Exit:
    Exit Function

Mainform_Close_Err:
    MsgBox Error$
    Resume Mainform_Close_Exit

End Function


'------------------------------------------------------------
' Mainform_Show
'
'------------------------------------------------------------
Function Mainform_Show()
On Error GoTo Mainform_Show_Err

    DoCmd.OpenForm "Main", acNormal, "", "", , acNormal


Mainform_Show_Exit:
    Exit Function

Mainform_Show_Err:
    MsgBox Error$
    Resume Mainform_Show_Exit

End Function

[trey3143]

just fixed it thanks for helping

[NeoPa]

I reformatted your SQL so it was legible before looking more closely at the error message and realising it was in your stLinkCriteria line (or, now I reformated that for you, three lines).

You use [Tester_Info.TNR CC_Reg_Date] which should really be [Tester_Info].[TNRCC_Reg_Date] or Tester_Info.TNR CC_Reg_Date. That will fix your problem.

[trey3143]

thats exactly what I did, thanks for all the help, bytes.com is a life saver

[NeoPa]

Very pleased to hear it helped. Thanks for posting :)