Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 15th, 2011, 12:31 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default New employee: Access qry won't bring back value?

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
 
Old November 15th, 2011, 12:40 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Can you post the SQL for the Query?

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,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
 
Old November 15th, 2011, 12:47 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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;
 
Old November 15th, 2011, 12:51 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 15th, 2011, 01:24 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Try this and see if it works for you...

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,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

Last edited by gjgriffith; November 15th, 2011 at 01:30 PM..
 
Old November 15th, 2011, 01:33 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by SunnySide View Post
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.
 
Old November 15th, 2011, 01:35 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 15th, 2011, 01:39 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default What is the actual VBA code?

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,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
 
Old November 15th, 2011, 01:45 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default

What do you mean by "specific VBA Code"? would it be different than the code I gave?
 
Old November 15th, 2011, 01:48 PM
Authorized User
 
Join Date: Nov 2011
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
employee leave update... vamshidhar ASP.NET 3.5 Professionals 0 July 12th, 2011 02:52 PM
Select the top 3 record of each employee phungleon Access 1 June 16th, 2007 01:16 AM
Select employee name spelling mateenmohd SQL Server 2000 21 June 7th, 2004 02:23 AM
Prb: COUNT qry result NOT SAME as SELECT qry savoym SQL Language 5 July 2nd, 2003 04:44 PM
Bring back the purple folders! Jeff Mason Forum and Wrox.com Feedback 20 June 19th, 2003 12:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.