SQL statement returning #Name?

[Ace71425]

Hello...I'm trying to run an SQL statement to make a textbox equal a search of a query based on criteria in labels in my form..Now this SQL statement works from Excel to Access but not in Access in my form. I'm thinking that it has to do with in Excel the field I was searching to match Datestamp was actually date formatted and of course the label in the Access form is not a formatted date

Me.Text371.ControlSource = "SELECT SumOfExpr3 FROM MakePercentages WHERE Datestamp = " & Day27.Caption & " And Rep = " & Label7.Caption & ""

So Rep is a text format and Datestamp is a date format in the table from where the query grabs it from...I need to make that Text371 equal to one of the query results in MakePercentages based on those conditions. Any ideas on what is going wrong with the formatting of this statement here as I know the statement itself does work just coming from Excel. Thanks! Let me know if you need more info.

 

[Ace71425]

My mistake both the fields from the table are text formats either way what's going on with this statement lol

 

[james_lankford]

My mistake both the fields from the table are text formats either way what's going on with this statement lol

then try this

Me.Text371.ControlSource = "SELECT SumOfExpr3 FROM MakePercentages WHERE Datestamp = '" & Day27.Caption & "' And Rep = '" & Label7.Caption & "' "

there are some single quotes mixed with double quotes in there so make sure you copy it correctly

 

[Ace71425]

I'll try that first thing tomorrow morning...if it works you are a scholar sir because that was 5 failed hours of my day today

 

[Ace71425]

No go...it's still coming up with #Name?

 

[Ace71425]

Nothing I try works....I don't understand why Access has to make it so difficult to just have a **** textbox equal to a search of a query

 

[Micron]

Well, to say DateStamp is text is kind of bizarre 'cuz it sure smells like a date. If they are dates, the sql statement won't work as written - you need to swap the ' for #.
Substitute valid values for your variables and paste the sql into sql view of a new query and run it. If it returns more than one row, that is your problem. You cannot assign a recordset to a textbox. Try something like:
"SELECT SumOfExpr3 FROM MakePercentages WHERE Datestamp = 'DOG' And Rep = 'CAT'"

Rather than directly assign a sql string to an object, it is better to assign it to a variable so you can check the processed result in the vb editor immediate window:

Dim strSql as String
strSql = "SELECT SumOfExpr3 FROM MakePercentages WHERE Datestamp = '" & Day27.Caption & "' And "
strSql = strSql & "Rep = '" & Label7.Caption & "' 
Me.Text371.ControlSource = strSql

You put a break on the line after the assignment, then when execution stops there, type ?strSql and hit RETURN (in the immediate window) to see the construct. You can mouse over simple variable assignments to see the values, but long strings won't show.
I do not use line continuation characters in sql constructs - too messy for me.

In addition, the sql looks strange, but then I don't know what you're querying. I'm used to seeing something more like
SELECT Sum(tablename1.fieldname1) AS Something WHERE...GROUP BY tablename2.fieldname2;