Excel for Life: 25 new messages in 13 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Help- Calling an ILX Excel function in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a3e8e9b40a52b038?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 11:21 am
From: Nate

Does anyone have any thoughts or experience with this?

"Nate" wrote:

> I am trying to call an ILX function (i.e. =ILX|Q!'ONFD-GA,BID') in an Access
> Form. In Excel, this function pulls live data from a financial server
> (Thompson TradeWeb) and gives the current quote. I am attempting to move
> information to Access and was wondering if it is possible to pull live data
> in a database. If it is possible, will the link update as the data changes?
> From reading other questions in this forum, it sounds like the best way of
> doing this would be to call an Excel function. My VB skills are lacking, so
> any help would be greatly appreciated.
>
> Thanks!

==============================================================================
TOPIC: usps api's?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/994f314776a9c2b7?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 11:36 am
From: C Hayes

Does anyone know about using usps api's for scrubbing address data?

I've got 15,000 + records I'd like to cleanse about twice a year. I was
hoping to write some code against a usps api.

Is this possible in access?

==============================================================================
TOPIC: When all records in a subform combo box are a certain value, f
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e03e744c4d3df358?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 11:54 am
From: mattieflo

Great thanks Stuart.

"Stuart McCall" wrote:

> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
> news:E3133C56-7802-4B05-AAFE-08CB3C2D9F88@microsoft.com...
> > Hi Stuart,
> >
> > I just realized that EMAIL is also a subform. They are linked together and
> > EMAIL is the parent form and PURCHASE ORDERS is the child form. I still
> > want
> > it to do the same thing, but I'm not writing the code right that you
> > provided. Could you help me with how to reference that subform? Thanks.
> >
> > "Stuart McCall" wrote:
> >
> >> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
> >> news:9E27FF53-D475-4758-AD63-BCEEAA10B1D5@microsoft.com...
> >> > Hello,
> >> >
> >> > I have a form named EMAIL and on it is a subform named PURCHASE ORDERS.
> >> > Each
> >> > EMAIL has several PURCHASE ORDERS. On the PURCHASE ORDER subform, there
> >> > is
> >> > a
> >> > drop down box that indicates Pending or Completed. I want to program
> >> > this
> >> > so
> >> > that when all PURCHASE ORDERS are set to "Completed" that it will flag
> >> > a
> >> > checkbox on the EMAIL main form. Does anyone have any suggestions on
> >> > how
> >> > to
> >> > go about this? Thanks!
> >>
> >> Use code like this in your main form's Current event:
> >>
> >> Dim CompletedCount As Long
> >> Dim SubformCount As Long
> >>
> >> CompletedCount = Dcount("*","PURCHASE ORDERS","ComboField = 'Completed'")
> >> SubformCount = Me.SubformControlName.Form.Recordcount
> >> Me.CheckboxName.Value = (CompletedCount = SubformCount)
> >>
> >> (alter the control names accordingly, of course)
>
> Ok, if I understand you correctly, getting the SubformCount ought to go
> something like this:
>
> SubformCount =
> Me.EMAILSubformControlName.Form.PURCHASEORDERSSubformControlName.Form.Recordcount
>
> (that's one line of code)
>
>
>
>
>
>

==============================================================================
TOPIC: Database opens slowly.
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b13aaafc45780317?hl=en
==============================================================================

== 1 of 3 ==
Date: Tues, Nov 20 2007 12:21 pm
From: "Tony Toews [MVP]"

RobGMiller <RobGMiller@discussions.microsoft.com> wrote:

>I tried to decompile but it tells me that the VBA project in the database is
>corrupt.

The decompile is what I would do first in your situation but you got a
corrupt VBA message. I'd suggest importing the database into a new
MDB and then trying the decompile.

Note that orphaned compiled code, which decompile will strip out, gets
copied across when you create an MDE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

== 2 of 3 ==
Date: Tues, Nov 20 2007 2:00 pm
From: RobGMiller

I've tried to open an unbound splash form as soon as the Autoexec is run or
by making it the primary display form. Either methods takes about as
objectionably long to reach the code that opens the form.

I think there is something wrong other than the size of it. Do you know of a
place where the startup process prior to the Autoexec or Opening the primary
display form might be found.

I did get an error when trying to decompile. However, I don't know how to
get around it. Incidentally, the decompile, compact and recompile method used
to clean up the database and speed it up a bit did corrupt it to the point
where I had to go back to the backup.

I might have to remove so code one bit at a time to see which code the
decompile process objects to.

--
RobGMiller

"ruralguy via AccessMonster.com" wrote:

> You should be able to open a Splash form (that has no RecordSource) quickly
> and then open the other form invisible and when loaded, turn on the
> visibility and close the Splash form.
>
> RobGMiller wrote:
> >Yes it is.
> >
> >So is the Access data container for the purpose of this test. Putting it on
> >a mapped network drive does not affect the opening time and it has a minor
> >effect on data access generally.
> >
> >> Your MDE *is* on the local drive, right?
> >>
> >[quoted text clipped - 18 lines]
> >> >> >
> >> >> >Thanks for your help.
>
> --
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
>
>

== 3 of 3 ==
Date: Tues, Nov 20 2007 4:19 pm
From: "ruralguy via AccessMonster.com"

Did you try Tony's suggestion of copying importing everything into a new,
fresh mdb?

RobGMiller wrote:
>I've tried to open an unbound splash form as soon as the Autoexec is run or
>by making it the primary display form. Either methods takes about as
>objectionably long to reach the code that opens the form.
>
>I think there is something wrong other than the size of it. Do you know of a
>place where the startup process prior to the Autoexec or Opening the primary
>display form might be found.
>
>I did get an error when trying to decompile. However, I don't know how to
>get around it. Incidentally, the decompile, compact and recompile method used
>to clean up the database and speed it up a bit did corrupt it to the point
>where I had to go back to the backup.
>
>I might have to remove so code one bit at a time to see which code the
>decompile process objects to.
>
>> You should be able to open a Splash form (that has no RecordSource) quickly
>> and then open the other form invisible and when loaded, turn on the
>[quoted text clipped - 11 lines]
>> >> >> >
>> >> >> >Thanks for your help.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

==============================================================================
TOPIC: how to loop through all tables in an Access dataset
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7c7c53d0de384411?hl=en
==============================================================================

== 1 of 7 ==
Date: Tues, Nov 20 2007 12:33 pm
From: chun

Hi,

Can anybody show me how to loop through all tables in a Access dataset? The
object model for Access seems to be harder to understand than that for Excel,
why is that?

Thanks!!!

--
For the freedom in MS Office!

== 2 of 7 ==
Date: Tues, Nov 20 2007 12:44 pm
From: SHIPP

The following loops through all tables to see if a table exists.

TO SEE IF A TABLE EXISTS

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next I
Set db = Nothin
End Function

--
M. Shipp

"chun" wrote:

> Hi,
>
> Can anybody show me how to loop through all tables in a Access dataset? The
> object model for Access seems to be harder to understand than that for Excel,
> why is that?
>
> Thanks!!!
>
> --
> For the freedom in MS Office!

== 3 of 7 ==
Date: Tues, Nov 20 2007 12:45 pm
From: Roger Converse

Something like this should work.

Sub TableisThere(booIsThere As Boolean, TableName As String)

Dim tbldef As TableDef, tbldefs As TableDefs

Set tbldefs = CurrentDb.TableDefs

booIsThere = False

For Each tbldef In tbldefs

If tbldef.Name = TableName Then
'We found the proper table,
booIsThere = True
Exit Sub
End If

Next tbldef

End Sub

"chun" wrote:

== 4 of 7 ==
Date: Tues, Nov 20 2007 12:47 pm
From: chun

I wonder if I can loop through all the tables just using the Access objects,
without involving DAO. Can you hlep?
--
For the freedom in MS Office!

"SHIPP" wrote:

> The following loops through all tables to see if a table exists.
>
> TO SEE IF A TABLE EXISTS
>
> Function fExistTable(strTableName As String) As Integer
> Dim db As Database
> Dim i As Integer
> Set db = DBEngine.Workspaces(0).Databases(0)
> fExistTable = False
> db.TableDefs.Refresh
> For i = 0 To db.TableDefs.Count - 1
> If strTableName = db.TableDefs(i).Name Then
> 'Table Exists
> fExistTable = True
> Exit For
> End If
> Next I
> Set db = Nothin
> End Function
>
> --
> M. Shipp
>
>
> "chun" wrote:
>
> > Hi,
> >
> > Can anybody show me how to loop through all tables in a Access dataset? The
> > object model for Access seems to be harder to understand than that for Excel,
> > why is that?
> >
> > Thanks!!!
> >
> > --
> > For the freedom in MS Office!

== 5 of 7 ==
Date: Tues, Nov 20 2007 1:00 pm
From: "Albert D. Kallal"

"chun" <chun@discussions.microsoft.com> wrote in message
news:7860FBD7-0B27-4337-8751-844FE0370BF9@microsoft.com...
> Hi,
>
> Can anybody show me how to loop through all tables in a Access dataset?

You actually don't necessarily have to use a dataset, there is a so called
collection of all tables that we normally use.

eg:

Dim T As TableDef

For Each T In CurrentDb.TableDefs
Debug.Print T.Name
Next T

> The
> object model for Access seems to be harder to understand than that for
> Excel,
> why is that?

if you can answer the following question, then you'll be able to understand
your answer. Why is it more difficult to build a garage that it is to build
a dog house? after all, in both cases are simply using hammers and nails.
can you answer this question for me? if you can solve an answer this
question, then you'll have exactly the same answer as to when accessed model
is considerably more difficult than that of Excel.

In a nutshell, it's simply that MS access is a considerably more complex
product. MS access is a database system, and database systems are what we
use to manage information. Your local bank certainly will not keep all their
accouting data in a spreadsheet. However, I can bet you a million dollars
that that bank keeps its business account information in a database.
Database systems by their nature are more complex products than are
spreadsheets.

Building a garage is a considerably more difficult task then it is to build
a dog house. It should not be one of life's great mystries to you that it's
more difficult to build a garage, then it is a dog house.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 6 of 7 ==
Date: Tues, Nov 20 2007 1:12 pm
From: chun

I think I understand it a little bit now. Thank you so much for taking the
time to respond my question!

--
For the freedom in MS Office!

"Albert D. Kallal" wrote:

> "chun" <chun@discussions.microsoft.com> wrote in message
> news:7860FBD7-0B27-4337-8751-844FE0370BF9@microsoft.com...
> > Hi,
> >
> > Can anybody show me how to loop through all tables in a Access dataset?
>
> You actually don't necessarily have to use a dataset, there is a so called
> collection of all tables that we normally use.
>
> eg:
>
> Dim T As TableDef
>
> For Each T In CurrentDb.TableDefs
> Debug.Print T.Name
> Next T
>
>
>
> > The
> > object model for Access seems to be harder to understand than that for
> > Excel,
> > why is that?
>
> if you can answer the following question, then you'll be able to understand
> your answer. Why is it more difficult to build a garage that it is to build
> a dog house? after all, in both cases are simply using hammers and nails.
> can you answer this question for me? if you can solve an answer this
> question, then you'll have exactly the same answer as to when accessed model
> is considerably more difficult than that of Excel.
>
> In a nutshell, it's simply that MS access is a considerably more complex
> product. MS access is a database system, and database systems are what we
> use to manage information. Your local bank certainly will not keep all their
> accouting data in a spreadsheet. However, I can bet you a million dollars
> that that bank keeps its business account information in a database.
> Database systems by their nature are more complex products than are
> spreadsheets.
>
> Building a garage is a considerably more difficult task then it is to build
> a dog house. It should not be one of life's great mystries to you that it's
> more difficult to build a garage, then it is a dog house.
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
>
>
>
>

== 7 of 7 ==
Date: Tues, Nov 20 2007 1:27 pm
From: "Dirk Goldgar"

"chun" <chun@discussions.microsoft.com> wrote in message
news:91A46BD4-4BB4-4AB5-8A1D-E173143DFB1A@microsoft.com...
>I wonder if I can loop through all the tables just using the Access
>objects,
> without involving DAO. Can you hlep?

Yes, in all versions Access 2000 forward, there's a built-in collection you
can use:

Dim ao As AccessObject

For Each ao In CurrentData.AllTables
Debug.Print ao.Name
Next ao

However, there's not a whole lot you can do with an AccessObject, so most
often you'll end up using DAO anyway.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

==============================================================================
TOPIC: MS-Access - File Import Specifications...
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c73e3d40cce6e9cc?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:38 pm
From: chun

Try Schema, which is some kind of file defining the layout of the files you
intend to import. Then you need some vba coding to do the import.
--
For the freedom in MS Office!

"JEB" wrote:

> I have a rather large (2300 byte) file layout of Billing Data that I need to
> import into Access, field by field. The definition of this file exists in a
> Cobol Copybook. Assuming that the Copybook can be translated correctly into
> a form that represents the import wizard, can you directly import the file
> definition into Access? If so, how?
> Thank you.
>
> JEB

==============================================================================
TOPIC: Visible = True Vs Visble = False
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ab35e58d40612388?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:42 pm
From: dsc2bjn

I will try it and see how it works, but I have created a pseudo-workaround.

I increased the height of all the headers to allow for the height of the
first header group print completely (granted if someone enters a longer
values it would still run over).

This wil buy me some time to attempt your solution.

"Marshall Barton" wrote:

> dsc2bjn wrote:
> >I think I did what you suggested.
>
>
> Forget that earlier partial workarounf, I wasn't done with
> it and did not mean to send it. The problem is even messier
> than I thought it would be.
>
> It turns out that the best I could do ran into another issue
> when one of the headers grow and the detail grows, then
> Access will grow the detail too much. It works well if the
> detail grows but none of the headers grow. It also works if
> any header grows but the detail does not grow.
>
> The messiness starts by adding an unbound hidden text box
> named txtMaxHdr to the top level group header. This will be
> set to the height of the tallest header.
>
> The next step is to create a group header section named
> DummyHdr for the detail sorting level. (If you are not
> sorting the details, create a group level on the PK field.)
> Add a text box named txtLine to this section and set its
> control source to =1 and RunningSum property to Over Group.
> This will be used to determine the first detail after one of
> the other headers has a new value. Make this header section
> invisible because it is only used to help the code.
>
> Hear's a copy of my report module. I hope you can unravel
> the line wrapping.
> -----------------------------------------------------------
> Private Sub Detail_Format(Cancel As Integer, FormatCount As
> Integer)
> If Me.txtLine = 1 And Me.Section(0).Height < txtMaxHdr
> Then
> Me.Section(0).Height = txtMaxHdr
> End If
> txtMaxHdr = 0
> End Sub
>
> Private Sub Detail_Print(Cancel As Integer, PrintCount As
> Integer)
> Me.Line (Me.EmployeeID.Left, 0)
> -Step(Me.EmployeeID.Width, Me.Height), , B
> Me.Line (Me.AbsenceDate.Left, 0)
> -Step(Me.AbsenceDate.Width, Me.Height), , B
> Me.Line (Me.Reason.Left, 0)-Step(Me.Reason.Width,
> Me.Height), , B
> End Sub
>
> Private Sub DummyHdr_Format(Cancel As Integer, FormatCount
> As Integer)
> Me.Section(0).Height = 0 'reduce detail size in case
> it was changed
> End Sub
>
> Private Sub EmployeeHdr_Print(Cancel As Integer, PrintCount
> As Integer)
> txtMaxHdr = Me.Height 'top group
> Me.MoveLayout = False
> End Sub
>
> Private Sub AbsenceHdr_Print(Cancel As Integer, PrintCount
> As Integer)
> If Me.Height > txtMaxHdr Then txtMaxHdr = Me.Height
> Me.MoveLayout = False 'intermediate groups
> End Sub
> -----------------------------------------------------------
> I tested that code in A2003. I think it should work in
> A2002, but don't know what will happen in A2007. It can not
> work in A2k or earlier versions. If you are using A97 or
> A2K then I might or might not be able to think up another
> way, but would rather not try unless it's necessary.
>
> --
> Marsh
> MVP [MS Access]
>

==============================================================================
TOPIC: find files on hard drive from a list field
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b07605a1f34bfb2?hl=en
==============================================================================

== 1 of 2 ==
Date: Tues, Nov 20 2007 12:45 pm
From: chun

You may want to follow the following steps:
1. put the list of files in a Access table.
2. open the table as a recordset, loop through each record and retrive the
file name, including the path.
3. open the folder where the files reside, loop through each file in the
folder, if the file name macth that from the recordset, move them, otherwise
ignore the file and move to the next file.

Hope this helps.

--
For the freedom in MS Office!

"Isaac" wrote:

> I have a need to move (or copy) certain files on my hard drive in a
> particular folder into another folder. Not all files in that folder will be
> moved/copied. I want to have only those files whose name is listed in a
> field in a table moved. I know can do this one record at a time, but would
> like it to be automated so that all files listed in that field are moved with
> a single click.
> I have no idea how to do this. Can anyone help.

== 2 of 2 ==
Date: Tues, Nov 20 2007 2:50 pm
From: Isaac

I already know the logic to use in the process. What I don't know is how to
perform step 3, especially the part of moving the file programmatically. I
know how to open a folder, but I don't want to be in the loop. I want the
program to do it all until it reaches an EOF.

"chun" wrote:

> You may want to follow the following steps:
> 1. put the list of files in a Access table.
> 2. open the table as a recordset, loop through each record and retrive the
> file name, including the path.
> 3. open the folder where the files reside, loop through each file in the
> folder, if the file name macth that from the recordset, move them, otherwise
> ignore the file and move to the next file.
>
> Hope this helps.
>
> --
> For the freedom in MS Office!
>
>
> "Isaac" wrote:
>
> > I have a need to move (or copy) certain files on my hard drive in a
> > particular folder into another folder. Not all files in that folder will be
> > moved/copied. I want to have only those files whose name is listed in a
> > field in a table moved. I know can do this one record at a time, but would
> > like it to be automated so that all files listed in that field are moved with
> > a single click.
> > I have no idea how to do this. Can anyone help.

==============================================================================
TOPIC: DateDiff
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/747b4c1e77e6252d?hl=en
==============================================================================

== 1 of 2 ==
Date: Tues, Nov 20 2007 12:49 pm
From: Roger Converse

Thanks!

John, I was hoping that Access had somethig for holidays. With the weekends
taken care of, updating a table once a year is no big deal.

Thanks again.

Roger

"John W. Vinson" wrote:

> On Mon, 19 Nov 2007 09:34:01 -0800, Roger Converse
> <RogerConverse@discussions.microsoft.com> wrote:
>
> > Ir eally don't want to have to update a table every
> >year if possible.
>
> Then you will need to create a table for the movable holiday dates in every
> year that you expect the application to be in operation. You can certainly
> program to have December 25th as a holiday (if your company observes
> Christmas); but other variable holidays - Mondays after a Sunday holiday, say
> - may be harder to calculate.
>
> IOW there is nothing built in to Access which identifies a date as "a date
> that your company observes as a holiday" - you have to provide that
> information!
>
> John W. Vinson [MVP]
>

== 2 of 2 ==
Date: Tues, Nov 20 2007 2:24 pm
From: "Douglas J. Steele"

See whether my June, 2005 "Access Answers" column in Pinnacle Publication's
"Smart Access" (a followup to the September, 2004 column) helps. You can
download the columns (and sample databases) for free from

http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Roger Converse" <RogerConverse@discussions.microsoft.com> wrote in message
news:87E03353-7019-4355-B94C-3174356ADA58@microsoft.com...
> Thanks!
>
> John, I was hoping that Access had somethig for holidays. With the
> weekends
> taken care of, updating a table once a year is no big deal.
>
> Thanks again.
>
> Roger
>
>
>
> "John W. Vinson" wrote:
>
>> On Mon, 19 Nov 2007 09:34:01 -0800, Roger Converse
>> <RogerConverse@discussions.microsoft.com> wrote:
>>
>> > Ir eally don't want to have to update a table every
>> >year if possible.
>>
>> Then you will need to create a table for the movable holiday dates in
>> every
>> year that you expect the application to be in operation. You can
>> certainly
>> program to have December 25th as a holiday (if your company observes
>> Christmas); but other variable holidays - Mondays after a Sunday holiday,
>> say
>> - may be harder to calculate.
>>
>> IOW there is nothing built in to Access which identifies a date as "a
>> date
>> that your company observes as a holiday" - you have to provide that
>> information!
>>
>> John W. Vinson [MVP]
>>

==============================================================================
TOPIC: How do I return a Percentile Rank in Microsoft Access?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ae9f54d2818421f3?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:57 pm
From: bobmount

Thanks, Gary....It seems like your query WOULD achieve what I'm after, but I
can't seem to get the syntax right for use within an existing "Make Table"
query in Access.

Basically, I'd like to simply add the percentile_rank fields (as, I think,
you've defined it) to the Access Query that's currently defined by the
following SQL:

SELECT Category, Name, Value INTO Product_1
FROM DataSet
WHERE (((Category)="Product_1"));

Would you mind integrating your SQL into this structure? I've tried
fiddling with it for hours, and can't seem to get it right....

Thank you sooooo much (in advance!)....

"Gary Walter" wrote:

>
> "bobmount" wrote:
> > I'm trying to add a field to a "Make Table" query in Access which will add
> > a
> > column representing the percentile rank of the [Value] field, based on the
> > other values in that column (i.e., 1%-100%)....similar to the PERCENTILE
> > RANK
> > feature in Excel. Does anyone know how to do this? (Assume my table
> > name
> > is 'DataSet', and the field with the values to rank is [Value])....
> >
> Hi Bob,
>
> Are we talking about:
>
> Percentile = (Rank/TotalCount) * 100
>
> The following solution requires that you
> be able to use DCount to produce a rank
> and a total count over the group.
>
>
> SELECT
> D.[Value],
> DCount("*","DataSet", "[Value]<" & D.[Value] ) AS ValRank,
> DCount("*","DataSet") AS TotCnt,
> ([ValRank]/[TotCnt])*100 AS Percentile
> FROM DataSet AS D;
>
> the above won't resolve ties though (but can with
> some other field added to DCount for ValRank...
>
> good luck,
>
> gary
>
>
>

==============================================================================
TOPIC: Rotate Tab Control
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a45bbceaeb9a204f?hl=en
==============================================================================

== 1 of 3 ==
Date: Tues, Nov 20 2007 2:09 pm
From: KJ-clueless

Is there any way to rotate or otherwise change orientation of a tab control
so that it's tabs appear on the left side rather than across the top? I've
looked in several forumns with no luck.

Thanks in advance

== 2 of 3 ==
Date: Tues, Nov 20 2007 2:22 pm
From: "Douglas J. Steele"

I don't believe it is possible.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"KJ-clueless" <KJclueless@discussions.microsoft.com> wrote in message
news:25A672D0-459D-4875-A9C9-6B4DD3FBF357@microsoft.com...
> Is there any way to rotate or otherwise change orientation of a tab
> control
> so that it's tabs appear on the left side rather than across the top?
> I've
> looked in several forumns with no luck.
>
> Thanks in advance

== 3 of 3 ==
Date: Tues, Nov 20 2007 2:44 pm
From: KJ-clueless

Thank you... unfortunately this is what I thought as well.

"Douglas J. Steele" wrote:

> I don't believe it is possible.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "KJ-clueless" <KJclueless@discussions.microsoft.com> wrote in message
> news:25A672D0-459D-4875-A9C9-6B4DD3FBF357@microsoft.com...
> > Is there any way to rotate or otherwise change orientation of a tab
> > control
> > so that it's tabs appear on the left side rather than across the top?
> > I've
> > looked in several forumns with no luck.
> >
> > Thanks in advance
>
>
>

==============================================================================
TOPIC: Search Form
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e59eb5434290efac?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 3:18 pm
From: mattieflo

Hello, I'm trying to write a search form that will search a main form for
fields. The main form consists of several of several subforms. The following
piece of code successfully searches for the Buyer Field on the main field.

Private Sub cmdSearchBuyer_Click()

Dim stbuyernumber As String
Dim stDocName As String
Dim stLinkCriteria As String

stbuyernumber = InputBox("Enter Buyer Number", "Search Buyer Number")
If stbuyernumber <> "" Then
stDocName = "frmMain2"
stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearch"
End If

A buyer can have many emails. I have an EMAIL subform that is linked to the
BUYER mainform. I want to search for the primary key of the email subform
(EmailID) so that it brings up the appropriate parent buyer number. Is there
a way to adjust this line in the code so it does that?

"stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber"

Thanks!

End Sub

==============================================================================
TOPIC: Show me function
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8c24e13f4016cce5?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 4:04 pm
From: pdouglas4294

Well, the vendor is Microsoft. I'm encountering it on SOME of the links
within Office 2K Help files. The links which will take you to another page
work. The links which expand/define a word do not. This problem is on two
seperate computers: An XPPRO Desktop and and XPPRO Laptop.
The Desktop, it had all of the Office 2K updates (including SP3). I
followed MS instructions and "Repaired" the installation. Problem persisted.
On the Laptop, again, it had all of the O2K Updates. This time, I
uninstalled Office, re-booted, re-installed, re-booted, applied the Office
2000 UA Control update (Refer article 262767), rebooted, and the problem
persisted.
This was before applying SR1 or SP3.

Internet Security Settings
Internet - Medium
Local Internet - Medium Low
Trusted Sites - Medium
Restricted Sites - High

Also running Symantec AV / Client Security 9.03 in a managed environment.
Have also tried it with the Fire wall dropped.

-Paul

"Tom van Stiphout" wrote:

> On Fri, 16 Nov 2007 11:29:01 -0800, Del
> <Del@discussions.microsoft.com> wrote:
>
> What did the help file or the vendor of this ocx say when you asked
> them?
>
> -Tom.
>
>
> >How do I turn on the "Show me" function that Ouactrl.ocx update disabled?
>

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Selasa, 20 November 2007

25 new messages in 13 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya