By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

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

P: 21
i have a database that keeps giving me the error The specified field [Tester_Info.TNRCC_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:
Expand|Select|Wrap|Line Numbers
  1. Case 2
  2. stLinkCriteria = "([CalExpires]>Now()) And ([Tester_Info.TNRCC_Reg_Date]>Now())"
  3. stLabel = "Current Backflow Tester Information"
  4. iColor = 32768
Here is the query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Tester_Info.TesterID,
  2.        ([LastName] & ", " & [FirstName]) AS Tester,
  3.        Tester_Info.TNRCC_Reg_Date AS State,
  4.        Calibration_Dates.Calibration_Date,
  5.        ([Calibration_Dates]![Calibration_Date]+365) AS [Good Until]
  6.  
  7. FROM   Tester_Info INNER JOIN Calibration_Dates
  8.   ON   Tester_Info.TesterID = Calibration_Dates.TesterID
  9.  
  10. WHERE  (((Tester_Info.TNRCC_Reg_Date)>=Now())
  11.   AND   ((Calibration_Dates.Calibration_Date)<=Now())
  12.   AND   ((([Calibration_Dates]![Calibration_Date]+365))>=Now())
Read more: ** Edit ** Illegal link to alternative forum removed as per site rules.
Jan 22 '10 #1

✓ answered by 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.TNRCC_Reg_Date] which should really be [Tester_Info].[TNRCC_Reg_Date] or Tester_Info.TNRCC_Reg_Date. That will fix your problem.

Share this Question
Share on Google+
9 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 23 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 24 '10 #3

P: 21
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
Jan 25 '10 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 25 '10 #5

P: 21
When I converted Macro in a module of the main form this is what it gave me
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. '------------------------------------------------------------
  4. ' Mainform_Close
  5. '
  6. '------------------------------------------------------------
  7. Function Mainform_Close()
  8. On Error GoTo Mainform_Close_Err
  9.  
  10.     DoCmd.Close acForm, "Main"
  11.  
  12.  
  13. Mainform_Close_Exit:
  14.     Exit Function
  15.  
  16. Mainform_Close_Err:
  17.     MsgBox Error$
  18.     Resume Mainform_Close_Exit
  19.  
  20. End Function
  21.  
  22.  
  23. '------------------------------------------------------------
  24. ' Mainform_Show
  25. '
  26. '------------------------------------------------------------
  27. Function Mainform_Show()
  28. On Error GoTo Mainform_Show_Err
  29.  
  30.     DoCmd.OpenForm "Main", acNormal, "", "", , acNormal
  31.  
  32.  
  33. Mainform_Show_Exit:
  34.     Exit Function
  35.  
  36. Mainform_Show_Err:
  37.     MsgBox Error$
  38.     Resume Mainform_Show_Exit
  39.  
  40. End Function
Jan 25 '10 #6

P: 21
just fixed it thanks for helping
Jan 25 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.TNRCC_Reg_Date] which should really be [Tester_Info].[TNRCC_Reg_Date] or Tester_Info.TNRCC_Reg_Date. That will fix your problem.
Jan 25 '10 #8

P: 21
thats exactly what I did, thanks for all the help, bytes.com is a life saver
Feb 23 '10 #9

NeoPa
Expert Mod 15k+
P: 31,186
Very pleased to hear it helped. Thanks for posting :)
Feb 25 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.