concatenating to a field name in a Access 2010 query

I have this query below that works:

SELECT Table1.First, Table1.Last, Table1.Street, Table1.City, Table1.State, Table1.Zip, Table1.[Phone #], Table1.[Dues '13]
FROM Table1
WHERE (((Table1.[Dues '13])<>0))
ORDER BY Table1.Last

I want to make it more generic so that as the years change it will always show the current year's membership.  I've tried this but it doesn't work:

SELECT Table1.First, Table1.Last, Table1.Street, Table1.City, Table1.State, Table1.Zip, Table1.[Phone #], Table1.[Dues+" '"+Format(now(), "yy")]
FROM Table1
WHERE (((Table1.[Dues+" '"+Format(now(), "yy")])<>0))
ORDER BY Table1.Last

It asks for an entry twice and then displays the entire table.  I understand why that happens.  Any suggestions on how to make it do what I want?
JoppaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AccessGuy1763Commented:
I just tested this out and within the context of trying to do it directly in query, I just don't think it works.  I made the following table:

tblTesting
FieldID: Autonumber
100913: Text

Then I tried this SQL:

SELECT tblTesting.FieldID, Format(Date(),"mmddyy")
FROM tblTesting;

Every time I did it, it didn't grab the field even though my expression comes out to the exact same field name.

What you're doing can be accomplished by directly modifying the SQL of the QueryDef like so:
Dim strSQL as string
Dim strCurrentYearFieldName as string

strCurrentYearFieldName =  "Table1.[Dues '" & Format(now(), "yy") & "]"

strSQL = "SELECT Table1.First, Table1.Last, Table1.Street, Table1.City, Table1.State, " & _
               "Table1.Zip, Table1.[Phone #], " & strCurrentYearFieldName & " " & _
               "FROM Table1 " & _
               "WHERE " & strCurrentYearFieldName & "<> 0 " & _
               "ORDER BY Table1.Last"

CurrentDB.QueryDefs("qryYourQueryName").SQL = strSQL

Open in new window

0
JoppaAuthor Commented:
Having an issue with implementation.  I tried copying the above code and placing it in the sql screen of the query.  It gives me the following error:

Invalid SQL statement; expected 'DELETE', 'INSERT', PROCEDURE', 'SELECT', or 'UPDATE'.

I searched on this and came up with a lot of things that were not helpful.  So I tried to place the code into a CASE statement associated with the switchboard.  I made the changes to call this case when selected.  That just errored too.

Here's what I did with the code above.  The only thing I can think of is the last line must be wrong.  I stepped through each line and the string is parsed and concatenated correctly.


Dim strSQL as string
Dim strCurrentYearFieldName as string

strCurrentYearFieldName =  "Table1.[Dues '" & Format(now(), "yy") & "]"

strSQL = "SELECT Table1.First, Table1.Last, Table1.Street, Table1.City, Table1.State, " & _
               "Table1.Zip, Table1.[Phone #], " & strCurrentYearFieldName & " " & _
               "FROM Table1 " & _
               "WHERE " & strCurrentYearFieldName & "<> 0 " & _
               "ORDER BY Table1.Last"

CurrentDB.QueryDefs("qryMembership 13").SQL = strSQL

Membership 13 is the name of the query I'm working with in Access.


Thanks
0
AccessGuy1763Commented:
I think you misunderstood part of my example and this might not be the only problem... but the "qry" part of my query "qryYourQueryName" is part of the name, not some prefix necessary as part of the code.  Many people use prefixes in their naming conventions so that when you're referring to those variables/objects, it's easier to understand exactly what you're referring to.

If your query name is just "Membership 13", remove the "qry" from the query name in the code you posted.  Also, I might recommend adding a "debug.print strSQL" right before you attempt to set the querydef.  That way, if there is a problem with the constructed SQL, you can copy it out of the immediate window and paste it into a blank query for testing.

Edit: WHOA, just realized that in your first line of the response you seem to be trying to paste all of this VBA code directly into a query.  That will absolutely not work.  This code is meant to be behind a command button or something.  The intent of it is to dynamically construct a SQL statement which can be used to change the underlying SQL of an existing query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoppaAuthor Commented:
Sorry it took so long to respond but I was having problems displaying the data and I wanted to figure out on my own.  

Using what you gave me above I got it working the way I want it.

Thanks for the help.
0
AccessGuy1763Commented:
Glad you got it working!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.