" is not a valid name (Error 3125) (1 Viewer)

Heatshiver · Sep 21, 2012

I have a query that is made up of calculations based on some basic numbers from a few tables (which all have relationships to one main table). Initially, I had about 8 other queries that all linked into this one. I checked those queries to see if the problem stemmed from there, but they run fine. I deleted those queries from this main query and tried to run the query with just the calculations present, but it still continues to give me this error.

From what I can tell, everything is spelled correctly and to the restrictions of what characters are allowed. I am not sure what the actual problem is as I don't seen anything that would be incorrect.

This is the SQL from it, I am hoping someone will spot something that should not be:

SELECT [Process-Q50]/([tblFluidSum]![Vol% Oil]/100) AS [Process-Q48], ([Process-Q82]*[tblFluidSum]![SG Base Fluid])*6.29 AS [Process-Q50], [Process-Q81]/1000 AS [Process-Q82], IIf([Process-R51]=0,(([Process-Q80]/[Process-Q53])*[Process-Q51]),([Process-Q80]/[Process-Q53])*(([Process-Q51]+[Process-R51])/2)) AS [Process-Q81], [Process-Q79]*1000 AS [Process-Q80], ([Process-Q78]*0.159)*2.6 AS [Process-Q79], (([tblGenSum]![Hole Dia.]^2/1029)*[Process-N10])*(1+([tblGenSum]![Washout %]/100)) AS [Process-Q78], [tblGenSum]![Daily End Depth]-[tblGenSum]![Daily Start Depth] AS [Process-N10], 100-([Process-Q51]+[Process-Q52]) AS [Process-Q53], (([tblSolidCtrlSum]![Total Wgt of Water (g) (CDRI1)]-[Process-Q58])/[Process-Q54])*100 AS [Process-Q52], ([Process-Q56]/[Process-Q57])*100 AS [Process-Q51], [tblSolidCtrlSum]![Empty Cell + Sample (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cell (g) (CDRI1)] AS [Process-Q54], [tblSolidCtrlSum]![Empty Cell + Sample + Water (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cell + Sample (g) (CDRI1)] AS [Process-Q58], [Process-Q55]-[tblSolidCtrlSum]![Total Wgt of Water (g) (CDRI1)] AS [Process-Q56], [tblSolidCtrlSum]![Wgt of Cyl + SF + Water (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cylinder (g) (CDRI1)] AS [Process-Q55], [tblSolidCtrlSum]![Wgt of Cell && Ash (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cell (g) (CDRI1)] AS [Process-Q57], [Process-Q48] AS [Process-R48], ([qryVolDiscMudSh1]![Process-D48]+[qryVolDiscMudSh2]![Process-E48]+[qryVolDiscMudSh3]![Process-F48]+[qryVolDiscMudSh4]![Process-G48]+[qryVolDiscMudSh5]![Process-H48]+[qryVolDiscMudSh6]![Process-I48]+[qryVolDiscMudSh7]![Process-J48]+[qryVolDiscMudSh8]![Process-K48])-[Process-Q48] AS [WellSummary-H13], ([qryVolDiscMudSh1]![Process-D48]+[qryVolDiscMudSh2]![Process-E48]+[qryVolDiscMudSh3]![Process-F48]+[qryVolDiscMudSh4]![Process-G48]+[qryVolDiscMudSh5]![Process-H48]+[qryVolDiscMudSh6]![Process-I48]+[qryVolDiscMudSh7]![Process-J48]+[qryVolDiscMudSh8]![Process-K48])-[Process-R48] AS [WellSummary-S13], [WellSummary-AM12]/[WellSummary-AN14] AS [WellSummary-AO12], [tblSolidCtrlSum]![Sample Depth (CD1)]-[tblGenSum]![Daily Start Depth] AS [WellSummary-AM12], [WellSummary-AM12]+[WellSummary-AM13] AS [WellSummary-AN14], [tblGenSum]![Daily End Depth]-[tblSolidCtrlSum]![Sample Depth (CD1)] AS [WellSummary-AM13], [WellSummary-AM13]/[WellSummary-AN14] AS [WellSummary-AO13], ((([WellSummary-H13]*[WellSummary-AO12])+([WellSummary-S13]*[WellSummary-AO13]))*158.98)/1000 AS [WellSummary-AD13]
FROM ((tblGenSum INNER JOIN tblDiscSum ON tblGenSum.GenSumPK = tblDiscSum.GenSumFK) INNER JOIN tblFluidSum ON tblGenSum.GenSumPK = tblFluidSum.GenSumFK) INNER JOIN tblSolidCtrlSum ON tblGenSum.GenSumPK = tblSolidCtrlSum.GenSumFK;

This has been driving me a bit insane considering how long it has already taken to create these calculations. Any help would be much appreciated!

YNWA · Sep 21, 2012

Your getting no where presenting so much code without any spacing.

Make the post layout better and more understandable.

Heatshiver · Sep 21, 2012

SELECT [Process-Q50]/([tblFluidSum]![Vol% Oil]/100) AS [Process-Q48],

([Process-Q82]*[tblFluidSum]![SG Base Fluid])*6.29 AS [Process-Q50],

[Process-Q81]/1000 AS [Process-Q82],

IIf([Process-R51]=0,(([Process-Q80]/[Process-Q53])*[Process-Q51]),([Process-Q80]/[Process-Q53])*(([Process-Q51]+[Process-R51])/2)) AS [Process-Q81],

[Process-Q79]*1000 AS [Process-Q80], ([Process-Q78]*0.159)*2.6 AS [Process-Q79],

(([tblGenSum]![Hole Dia.]^2/1029)*[Process-N10])*(1+([tblGenSum]![Washout %]/100)) AS [Process-Q78],

[tblGenSum]![Daily End Depth]-[tblGenSum]![Daily Start Depth] AS [Process-N10],

100-([Process-Q51]+[Process-Q52]) AS [Process-Q53],

(([tblSolidCtrlSum]![Total Wgt of Water (g) (CDRI1)]-[Process-Q58])/[Process-Q54])*100 AS [Process-Q52],

([Process-Q56]/[Process-Q57])*100 AS [Process-Q51],

[tblSolidCtrlSum]![Empty Cell + Sample (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cell (g) (CDRI1)] AS [Process-Q54],

[tblSolidCtrlSum]![Empty Cell + Sample + Water (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cell + Sample (g) (CDRI1)] AS [Process-Q58],

[Process-Q55]-[tblSolidCtrlSum]![Total Wgt of Water (g) (CDRI1)] AS [Process-Q56],

[tblSolidCtrlSum]![Wgt of Cyl + SF + Water (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cylinder (g) (CDRI1)] AS [Process-Q55],

[tblSolidCtrlSum]![Wgt of Cell && Ash (g) (CDRI1)]-[tblSolidCtrlSum]![Empty Cell (g) (CDRI1)] AS [Process-Q57], [Process-Q48] AS [Process-R48],

([qryVolDiscMudSh1]![Process-D48]+[qryVolDiscMudSh2]![Process-E48]+[qryVolDiscMudSh3]![Process-F48]+[qryVolDiscMudSh4]![Process-G48]+[qryVolDiscMudSh5]![Process-H48]+[qryVolDiscMudSh6]![Process-I48]+[qryVolDiscMudSh7]![Process-J48]+[qryVolDiscMudSh8]![Process-K48])-[Process-Q48] AS [WellSummary-H13],

([qryVolDiscMudSh1]![Process-D48]+[qryVolDiscMudSh2]![Process-E48]+[qryVolDiscMudSh3]![Process-F48]+[qryVolDiscMudSh4]![Process-G48]+[qryVolDiscMudSh5]![Process-H48]+[qryVolDiscMudSh6]![Process-I48]+[qryVolDiscMudSh7]![Process-J48]+[qryVolDiscMudSh8]![Process-K48])-[Process-R48] AS [WellSummary-S13],

[WellSummary-AM12]/[WellSummary-AN14] AS [WellSummary-AO12],

[tblSolidCtrlSum]![Sample Depth (CD1)]-[tblGenSum]![Daily Start Depth] AS [WellSummary-AM12],

[WellSummary-AM12]+[WellSummary-AM13] AS [WellSummary-AN14],

[tblGenSum]![Daily End Depth]-[tblSolidCtrlSum]![Sample Depth (CD1)] AS [WellSummary-AM13],

[WellSummary-AM13]/[WellSummary-AN14] AS [WellSummary-AO13],

((([WellSummary-H13]*[WellSummary-AO12])+([WellSummary-S13]*[WellSummary-AO13]))*158.98)/1000 AS [WellSummary-AD13]

FROM ((tblGenSum INNER JOIN tblDiscSum ON tblGenSum.GenSumPK = tblDiscSum.GenSumFK) INNER JOIN tblFluidSum ON tblGenSum.GenSumPK = tblFluidSum.GenSumFK) INNER JOIN tblSolidCtrlSum ON tblGenSum.GenSumPK = tblSolidCtrlSum.GenSumFK;

MSAccessRookie · Sep 21, 2012

Heatshiver said:
I have a query that is made up of calculations based on some basic numbers from a few tables (which all have relationships to one main table). Initially, I had about 8 other queries that all linked into this one. I checked those queries to see if the problem stemmed from there, but they run fine. I deleted those queries from this main query and tried to run the query with just the calculations present, but it still continues to give me this error.

From what I can tell, everything is spelled correctly and to the restrictions of what characters are allowed. I am not sure what the actual problem is as I don't seen anything that would be incorrect.

This is the SQL from it, I am hoping someone will spot something that should not be:

I have reformatted your code (see below), and off the top of my head, I see at least two types of things wrong here.

Missing DataSets (the Queries that you referred to?)
Misused Alias Values (an Alias cannot be used in the Query that defines it)

The Missing DataSet issue is easy to resolve. You can add the Datasets in the JOIN section of the Query. This approach may be a little more difficult to implement because it will require you to determine which Field to JOIN each Query on. You can also change the Select to use DLookups from the required Datasets. This approach may be easier to implement, but can also be much slower.

The Misused Alias Values issue is a little more tricky. You will either need to substitute the calculated values for the Alias when it is used, or it will involve the need for SubQueries. Note that only one example is provided, and that any others will need to be handled the same way.

Code:

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]SELECT [Process-Q50]/([tblFluidSum]![Vol% Oil]/100) AS [Process-Q48],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ([Process-Q82]*[tblFluidSum]![SG Base Fluid])*6.29 AS [Process-Q50],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [Process-Q81]/1000 AS [Process-Q82],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] IIf([Process-R51]=0,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     (([Process-Q80]/[Process-Q53])*[Process-Q51]),[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     ([Process-Q80]/[Process-Q53])*(([Process-Q51]+[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         [Process-R51])/2)) AS [Process-Q81],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [Process-Q79]*1000 AS [Process-Q80],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ([Process-Q78]*0.159)*2.6 AS [Process-Q79],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] (([tblGenSum]![Hole Dia.]^2/1029)*[Process-N10])*[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     (1+([tblGenSum]![Washout %]/100)) AS [Process-Q78],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblGenSum]![Daily End Depth]-[tblGenSum]![Daily Start Depth] AS [Process-N10],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] 100-([Process-Q51]+[Process-Q52]) AS [Process-Q53],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] (([tblSolidCtrlSum]![Total Wgt of Water (g) (CDRI1)] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [Process-Q58])/[Process-Q54])*100 AS [Process-Q52],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ([Process-Q56]/[Process-Q57])*100 AS [Process-Q51],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblSolidCtrlSum]![Empty Cell + Sample (g) (CDRI1)] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [tblSolidCtrlSum]![Empty Cell (g) (CDRI1)] AS [Process-Q54],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblSolidCtrlSum]![Empty Cell + Sample + Water (g) (CDRI1)] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [tblSolidCtrlSum]![Empty Cell + Sample (g) (CDRI1)] AS [Process-Q58],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [Process-Q55]-[tblSolidCtrlSum]![Total Wgt of Water (g) (CDRI1)] AS [Process-Q56],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblSolidCtrlSum]![Wgt of Cyl + SF + Water (g) (CDRI1)] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [tblSolidCtrlSum]![Empty Cylinder (g) (CDRI1)] AS [Process-Q55],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblSolidCtrlSum]![Wgt of Cell && Ash (g) (CDRI1)] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [tblSolidCtrlSum]![Empty Cell (g) (CDRI1)] AS [Process-Q57],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [Process-Q48] AS [Process-R48],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ([[COLOR=red][B]qryVolDiscMudSh1[/B][/COLOR]]![Process-D48] + [[COLOR=red][B]qryVolDiscMudSh2[/B][/COLOR]]![Process-E48] + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [[COLOR=red][B]qryVolDiscMudSh3[/B][/COLOR]]![Process-F48] + [[COLOR=red][B]qryVolDiscMudSh4[/B][/COLOR]]![Process-G48] + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [[COLOR=red][B]qryVolDiscMudSh5[/B][/COLOR]]![Process-H48] + [[COLOR=red][B]qryVolDiscMudSh6[/B][/COLOR]]![Process-I48] + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [[COLOR=red][B]qryVolDiscMudSh7[/B][/COLOR]]![Process-J48] + [[COLOR=red][B]qryVolDiscMudSh8[/B][/COLOR]]![Process-K48]) – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [Process-Q48] AS [WellSummary-H13],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ([[COLOR=red][B]qryVolDiscMudSh1[/B][/COLOR]]![Process-D48] + [[COLOR=red][B]qryVolDiscMudSh2[/B][/COLOR]]![Process-E48] + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [[COLOR=red][B]qryVolDiscMudSh3[/B][/COLOR]]![Process-F48] + [[COLOR=red][B]qryVolDiscMudSh4[/B][/COLOR]]![Process-G48] + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [[COLOR=red][B]qryVolDiscMudSh5[/B][/COLOR]]![Process-H48] + [[COLOR=red][B]qryVolDiscMudSh6[/B][/COLOR]]![Process-I48] + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [[COLOR=red][B]qryVolDiscMudSh7[/B][/COLOR]]![Process-J48] + [[COLOR=red][B]qryVolDiscMudSh8[/B][/COLOR]]![Process-K48]) – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [Process-R48] AS [WellSummary-S13],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [B][COLOR=purple][WellSummary-AM12][/COLOR]/[COLOR=purple][WellSummary-AN14][/COLOR][/B] AS [WellSummary-AO12],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblSolidCtrlSum]![Sample Depth (CD1)] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [tblGenSum]![Daily Start Depth] [COLOR=purple][COLOR=black]AS[/COLOR] [B][WellSummary-AM12][/B][/COLOR],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [B][COLOR=purple][WellSummary-AM12][/COLOR]+[COLOR=purple][WellSummary-AM13][/COLOR][/B] [COLOR=purple][COLOR=black]AS[/COLOR] [B][WellSummary-AN14][/B][/COLOR],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [tblGenSum]![Daily End Depth] – [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     [tblSolidCtrlSum]![Sample Depth (CD1)] AS [WellSummary-AM13],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [WellSummary-AM13]/[WellSummary-AN14] AS [WellSummary-AO13],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ((([WellSummary-H13]*[WellSummary-AO12]) + [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     ([WellSummary-S13]*[WellSummary-AO13]))*158.98)/1000 AS [WellSummary-AD13][/FONT][/COLOR]
[FONT=Verdana][COLOR=black]FROM ((tblGenSum [/COLOR][/FONT]
[COLOR=black][FONT=Verdana] INNER JOIN tblDiscSum [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     ON tblGenSum.GenSumPK = tblDiscSum.GenSumFK) [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] INNER JOIN tblFluidSum [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     ON tblGenSum.GenSumPK = tblFluidSum.GenSumFK) [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] INNER JOIN tblSolidCtrlSum [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] ON tblGenSum.GenSumPK = tblSolidCtrlSum.GenSumFK;[/FONT][/COLOR]
[/COLOR][/FONT]

Heatshiver · Sep 24, 2012

@MSAccessRookie thank you so much! I realize the other queries aren't included, but I didn't think about the aliases!

I will try this soon and report. Thanks again.

" is not a valid name (Error 3125) (1 Viewer)

Heatshiver

Registered User.

YNWA

Registered User.

Heatshiver

Registered User.

MSAccessRookie

AWF VIP

Heatshiver

Registered User.

Similar threads

Users who are viewing this thread