New employee: Access qry won't bring back value?

SunnySide · November 15th, 2011, 12:31 PM

i just started working for another company, and I have learned Access from using it. This has stumped me.

I am trying to do a simple formula: Month(Date).
But the value won't show. I was thinking it would be related to references, but it looks like everything is check that is normally checked.

Can someone help? I would think this would be an easy fix. I just haven't been able to find it.
Thanks in advance.
Crystal

gjgriffith · November 15th, 2011, 12:40 PM

Hello SunnySide,

I saw your posting here, thanks for placing it! It seems like what you are saying should work. You said:

Quote:

I am trying to do a simple formula: Month(Date)

I'm assuming that the "Date" parameter is referring to a date field, and if that is the case, this should work (no special references should be required)!?!?

Can you post the exact SQL statement of the query that you are trying to run? If so, I'll take a look and let you know what I think!

Thanks,

SunnySide · November 15th, 2011, 12:47 PM

That would be great. Thanks.

the actual VBA that I am trying to run is more complicated, so I wanted to simplify it as much as possible to see if I was doing something wrong. So this is what I used, and I'm still not getting anything back.
[code]
//SELECT PrevDate.CurrMonth, Month(Now()) AS PriorDate
//FROM PrevDate;

SunnySide · November 15th, 2011, 12:51 PM

I just wanted to let you know I will get a date for the CurrMonth Field, which comes from anther table. I don't get anything back on the formula in the PriorDate Field.

gjgriffith · November 15th, 2011, 01:24 PM

Hi SunnySide,

Thanks for the follow up. So, you said:

Quote:

...the actual VBA that I am trying to run is more complicated, so I wanted to simplify it as much as possible to see if I was doing something wrong.

Hmm, well, in your previous post, you said: "But the value won't show", so I was assuming you were having a problem with a Query object itself. What I like to do in a situation like this is create an Access Query first and run it to test it, just to make sure it is working. Once I'm confident the Query is working, then I try to debug the VBA code.

So, the SQL statement you've provided me here (which is commented out with C# style comments, and not VBA style comments) is:

Code:

//SELECT PrevDate.CurrMonth, Month(Now()) AS PriorDate
//FROM PrevDate;

This SQL statement is obviously broken right off the bat, because it has slashes in it (again, these are C/C++/C# style comments). Also, it is important to understand whether you are using the DAO or ADO libraries in VBA and how you want to return that data...but in this case (for this simple statement), it won't make a difference...so I'm guessing it is a problem in your VBA code other than this statement.

So, to try to help you, I created a simple table and query in Access (based on the parameters you've provided above), and the following SQL statement should work (as you expected):

Code:

SELECT PrevDate.CurrMonth, Month(Now()) AS PriorDate
FROM PrevDate;

And, if I add that SQL statement to an Access Query object (and the proper Table exists), this Query does indeed return data in Access.

So, again, my guess is that there is a problem in your VBA code. From your original statement of:

Quote:

But the value won't show.

I had assumed you were trying to "see" the data in an Access query object. Could you elaborate a little more about what is happening when you believe that the value isn't being "shown"? Otherwise, at this point, I think the VBA code would be helpful too.

Anyway, I hope that helps, but if you have more questions, please let me know and I'll do the best I can with the information provided to help solve the problem...

Sincerely,

SunnySide · November 15th, 2011, 01:33 PM

Quote:

Originally Posted by SunnySide

That would be great. Thanks.

the actual VBA that I am trying to run is more complicated, so I wanted to simplify it as much as possible to see if I was doing something wrong. So this is what I used, and I'm still not getting anything back.
[code]
SELECT PrevDate.CurrMonth, Month(Now()) AS PriorDate
FROM PrevDate;

I'm sorry to confuse things. I put those slashes in when I typed the message. It doesnt' have them in the code. I am running a query. When I execute the query the value brings back:
CurrMonth PriorMonth
11/15/2011 12:24:00PM

I ended up creating a new database, and made the table with the currmonth, and created a new qry asking it to bring back the PrevMonth and it still isn't bring a value back. I'm so stumped.

SunnySide · November 15th, 2011, 01:35 PM

I tryied to paste the table in, but it wouldn't copy over. The first column is CurrMonth with the date provided in that column, the second column is PriorMonth with nothing in the row.

gjgriffith · November 15th, 2011, 01:39 PM

Hi SunnySide,

I'm not sure what to say, I WAS able to get that SQL statement to work PERFECTLY (as I noted in my previous post...please read it carefully). What is the specific VBA code?

Thanks,

SunnySide · November 15th, 2011, 01:45 PM

What do you mean by "specific VBA Code"? would it be different than the code I gave?

SunnySide · November 15th, 2011, 01:48 PM

How do I find " DAO or ADO libraries in VBA "?