Kamis, 31 Juli 2014

RE: [MS_AccessPros] Query reference question

 

If the query yields one record, perhaps a dlookup(“Supervisor”,”qryMaxCompensatory”)

?

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, July 31, 2014 1:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query reference question

 



Jim-

 

When you use a reference like [Forms]![MyForm]![SomeControl], you are asking Access to go to the Forms collection (all open forms), find the form named “MyForm”, then use the value in the control named “SomeControl”.  There is no such thing as a Queries collection, so your reference dies with the very first part.

 

What is it you’re trying to do?  Reference a value in another open query?  Perhaps bind the query to a Form, then use the [Forms] syntax.

 

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

(Paris, France)

 

 

 

On Jul 31, 2014, at 3:21 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Hello,

 

I am trying to use a reference from a query like a Form reference. But what I am using is not working. I am getting a prompt for Queries. Is there something missing.

 

Thank You

 

=[Queries]![qryMaxCompensatory].[Supervisor] & "" & "Supervisor"

 

 

Jim Wagner

 






This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (3)
Did You Know?
Learn all about Conversations within groups

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Query reference question

 

Jim-


When you use a reference like [Forms]![MyForm]![SomeControl], you are asking Access to go to the Forms collection (all open forms), find the form named "MyForm", then use the value in the control named "SomeControl".  There is no such thing as a Queries collection, so your reference dies with the very first part.

What is it you're trying to do?  Reference a value in another open query?  Perhaps bind the query to a Form, then use the [Forms] syntax.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jul 31, 2014, at 3:21 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello,

I am trying to use a reference from a query like a Form reference. But what I am using is not working. I am getting a prompt for Queries. Is there something missing.

Thank You

=[Queries]![qryMaxCompensatory].[Supervisor] & "" & "Supervisor"

 
Jim Wagner

__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)
Did You Know?
Learn all about Conversations within groups

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Query reference question

 

Hello,

I am trying to use a reference from a query like a Form reference. But what I am using is not working. I am getting a prompt for Queries. Is there something missing.

Thank You

=[Queries]![qryMaxCompensatory].[Supervisor] & "" & "Supervisor"

 
Jim Wagner

__._,_.___
Posted by: Jim Wagner <luvmymelody@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)
Did You Know?
Learn all about Conversations within groups

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] Macro to open a PDF in Access 2013

 

My pleasure, Tim.

 

Regards,

Bill

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, July 31, 2014 10:48 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Macro to open a PDF in Access 2013

 

 

Thanks so much, Bill!

 

It works. You saved me a lot of time.

 

Tim

__._,_.___
Posted by: "Bill Mosca" <wrmosca@comcast.net>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (4)
Did You Know?
Learn all about Conversations within groups

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] Macro to open a PDF in Access 2013

 

Thanks so much, Bill!


It works. You saved me a lot of time.

Tim

__._,_.___
Posted by: timdbui@gmail.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (3)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] Macro to open a PDF in Access 2013

 

Tim

 

Easy-squeezy! Use FollowHyperlink like this:

1.      Open the form in design view

2.      Add a button

3.      Click on the button's click event

4.      Click the little builder button (...) to the right.

5.      Select Code Builder. That will open the code window to the Click event for the button.

6.      Put this line of code in the procedure:

 

Private Sub Command2_Click()

    Application.FollowHyperlink "YourFullPathToThePDFGoesHere"

   

End Sub

 

 

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT http://thatlldoit.com
MS Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My Nothing-to-do-with Access blog

http://wrmosca.wordpress.com

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, July 31, 2014 8:18 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Macro to open a PDF in Access 2013

 

 

Hi,

 

Would any of you please show me now to create a button so that when click, it will open a specific pdf? I can either place the PDF in a folder or attach it into the Access database somehow. I know how to create a button to open a form, but I prefer the PDF because of pictures and images on the PDF.

 

Thanks so much in advance!

 

Tim 

__._,_.___
Posted by: "Bill Mosca" <wrmosca@comcast.net>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] corrupted database

 

 

 

Gary,

That does help.  It will take some time to build the form but I will get started.

 

Also, one more question.  When the database does a compact and repair I seem to loose primary keys and relationships in about 5 tables, always the same tables.  I have to go in and fix them and then it all works well again.   Any Idea why that would happen?

 

Bill

 

MN

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 30, 2014 1:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] corrupted database

 

 

I had a similar issue on a multi-table database, where I need to verify
before/after maintenance or backup work.

I built a form where I have a field for each table I want to review.

Inside that field, I built an event using this code:

=DCount("[indexfield]","[tablename]")

An index field is used as there is always a value in every row. I believe
that
it only counts rows where that field is occupied.

I monitor about 40 different tables this way, and print out the form before
and
after. I added a field that displays the current date/time, which is
included in
the printout.

Hope this helps,
Gary

Original email:
-----------------
From: Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals]
MS_Access_Professionals@yahoogroups.com
Date: Wed, 30 Jul 2014 12:31:56 -0500
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] corrupted database

Every once in a while I have a problem with a database and it compacts and
repairs.

It then tells me to check to see if I have lost any records. Usually I know
what table to look at but I should check to see if there are any missing
records.

Is there any way to see how many records are in each table without opening
each table? It would really save me a lot of time.

Thanks for the help.

Bill

MN

----------------------------------------------------------
mail2web LIVE Free email based on Microsoft Exchange technology -
http://link.mail2web.com/LIVE

__._,_.___
Posted by: "Bill Singer" <Bill.Singer@at-group.net>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (3)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Macro to open a PDF in Access 2013

 

Hi,


Would any of you please show me now to create a button so that when click, it will open a specific pdf? I can either place the PDF in a folder or attach it into the Access database somehow. I know how to create a button to open a form, but I prefer the PDF because of pictures and images on the PDF.


Thanks so much in advance!


Tim 

__._,_.___
Posted by: timdbui@gmail.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Rabu, 30 Juli 2014

RE: [MS_AccessPros] Re: Question RE my FY query

 

Anytime a query suddenly asks for a parameter it's usually because you spelled a field name incorrectly. Check what the parameter is asking for and compare that to the actual field name.


Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com



---In MS_Access_Professionals@yahoogroups.com, <Sheryl.Jones@state.mn.us> wrote :

Ok, so I got that part to work, but now when I run the query, it is asking for parameter values instead of providing data.  Am I missing something?
 

__._,_.___
Posted by: wrmosca@comcast.net
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (18)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Re: Question RE my FY query

 

Sheryl-


If you want to show totals by Board, you need to include that field in the SELECT and GROUP BY clauses of the Crosstab query.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jul 30, 2014, at 3:11 PM, Sheryl.Jones@state.mn.us [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok, I know y'all are probably getting tired of my questions, but now that I have both the source query working and the crosstab query working, the data I was looking for does not separate as I was hoping so I'm fore confused than ever.  When I go back to the source query and run it,shows everything separated not combining the count in that query

sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab
CalendarMonth CalendarYear FY CaseID Date_Closed Board ClassRefSource
3 2008 2008 4449 3/6/2008 Nursing Third Party
7 2010 2011 5681 7/7/2010 Marriage & Family Therapy Board - Eligibility for Monitoring
10 2007 2008 3899 10/17/2007 Nursing Self-Report
10 2010 2011 5851 10/20/2010 Nursing Self-Report
2 2014 2014 7493 2/20/2014 Nursing Third Party
2 2012 2012 6175 2/7/2012 Nursing Board Action

and when I run the Cross tab query, it doesn't show the combined data either?

THOUGHTS?

sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab_Crosstab
FY Total Of CaseID Board - Eligibility for Monitoring Board - Follow-Up to Diagnosis/Treatment Board Action Self-Report Third Party
2008 449 60 78 53 207 51
2009 462 78 70 45 183 86
2010 489 87 64 62 201 75
2011 524 103 68 57 189 107
2012 455 91 37 56 193 78
2013 522 107 37 79 227 72
2014 527 120 55 83 189 80

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sheryl-

The name of the field in your source query is ClassRefSource, not Classification_Referral_Source.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jul 30, 2014, at 2:24 PM, Sheryl.Jones@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

OK.  I added your query as written, but now I am assuming that the crosstab query needs to be redone- because it doesn't work.  It says Teh MA dataabase engine does not reconize '[sj_query_to_cal_ref_by_brd_sourc_byFY_for_Crs_tab].Classification_Referral_Source' as a valid field name or expression

TRANSFORM Count([sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].CaseID) AS CountOfCaseID
SELECT [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].FY, Count([sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].CaseID) AS [Total Of CaseID]
FROM [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab]
GROUP BY [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].FY
PIVOT [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].Classification_Referral_Source;



---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Sheryl,
You can modify the SQL of a query so have any of the suggestions worked such as:
 
SELECT Month([Date_Closed]) AS CalendarMonth,
Year([Date_Closed]) AS CalendarYear,
IIf([CalendarMonth]>6,[CalendarYear]+1,[CalendarYear]) AS FY,
sj_qry_close_by_referral_by_fy.CaseID,
sj_qry_close_by_referral_by_fy.Date_Closed,
sj_qry_close_by_referral_by_fy.Board,
IIf(Classification_Referral_Source IN ("Board-FUDT","Board-Eligibility"), "Combined Name", Classification_Referral_Source) as ClassRefSource
FROM sj_qry_close_by_referral_by_fy
WHERE (((sj_qry_close_by_referral_by_fy.Date_Closed) Between #7/1/2007# And #6/30/2014#));
 
Also, can you set your emails to include the previous email?
 
Duane Hookom MVP
MS Access
 
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 30 Jul 2014 11:01:05 -0700
Subject: [MS_AccessPros] Re: Question RE my FY query



Unfortunately, I do not have the ability to adapt the tables in this case so I am trying to come up with a work around.


 

 

__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (17)

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)