Control Source in Form Doesn't Display Query

Hi- I just recently moved an expression that I had in only a Form to a
Query.  The Expression runs great and I renamed the Expression to
"Retiree Age:..."  On my Form- I go to change my Control Source to
"Retiree Age" and the age no longer comes up?  I don't know why it
won't but any input would be greatly appreciated.  I can't leave it in
the form b/c I need to generate a number of reports based on certain
ages.

I really appreciate this:

-Anthony Morano
Pension Fund Intern

0
antmorano
5/25/2007 4:27:10 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
1122 Views

Similar Articles

[PageSpeed] 48

<antmorano@gmail.com> wrote in message 
news:1180110430.088206.176650@q75g2000hsh.googlegroups.com...
> Hi- I just recently moved an expression that I had in only a Form to a
> Query.  The Expression runs great and I renamed the Expression to
> "Retiree Age:..."  On my Form- I go to change my Control Source to
> "Retiree Age" and the age no longer comes up?  I don't know why it
> won't but any input would be greatly appreciated.  I can't leave it in
> the form b/c I need to generate a number of reports based on certain
> ages.

If I were you I'd just delete the control that doesn't work.  Then I'd show 
the fields list and drag the Retiree age field to the form. 


0
Amy
5/25/2007 5:23:32 PM
You do not want to store an age in a table.  It will only be accurate for 
less than 1 year.  The correct technique is to store the birth date in the 
table and create a Public function to calculate the age where ever you need 
to display it.  It can be on a form, in a report, or in a query.

Here is an age calculation function

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

    If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
                Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
            Age = year(DateToday) - year(Bdate) - 1
    Else
            Age = year(DateToday) - year(Bdate)
    End If
End Function

for a form, you would call it from the Control Source
=Age(Me.txtBDay, Date())

for a report, it would be the same

for a query, you would create a calculated field:

Age: = Age([BIRTH_DATE], Date())




-- 
Dave Hargis, Microsoft Access MVP


"antmorano@gmail.com" wrote:

> Hi- I just recently moved an expression that I had in only a Form to a
> Query.  The Expression runs great and I renamed the Expression to
> "Retiree Age:..."  On my Form- I go to change my Control Source to
> "Retiree Age" and the age no longer comes up?  I don't know why it
> won't but any input would be greatly appreciated.  I can't leave it in
> the form b/c I need to generate a number of reports based on certain
> ages.
> 
> I really appreciate this:
> 
> -Anthony Morano
> Pension Fund Intern
> 
> 
0
Utf
5/25/2007 7:00:02 PM
"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
news:326A0EA9-FCC9-4C17-B7DB-49A6D5D31D2E@microsoft.com...
> You do not want to store an age in a table.  It will only be accurate for
> less than 1 year.  The correct technique is to store the birth date in the
> table and create a Public function to calculate the age where ever you 
> need
> to display it.  It can be on a form, in a report, or in a query.
>
> Here is an age calculation function
>
> Public Function Age(Bdate, DateToday) As Integer
> ' Returns the Age in years between 2 dates
> ' Doesn't handle negative date ranges i.e. Bdate > DateToday
>
>    If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
>                Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
>            Age = year(DateToday) - year(Bdate) - 1
>    Else
>            Age = year(DateToday) - year(Bdate)
>    End If
> End Function
>
> for a form, you would call it from the Control Source
> =Age(Me.txtBDay, Date())
>
> for a report, it would be the same
>
> for a query, you would create a calculated field:
>
> Age: = Age([BIRTH_DATE], Date())

Why couldn't you simply use the DateDiff function? 


0
Amy
5/25/2007 8:48:18 PM
It will not always show the correct number of years, depending on the birth 
date and the compare date.
For example, my birthday is today (it really is), so Had I done a datediff 
prior to today, it would return 64 while I was still 63.
So you have to take into account whether the compare date is before or after 
your birthday.
-- 
Dave Hargis, Microsoft Access MVP


"Amy Blankenship" wrote:

> 
> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
> news:326A0EA9-FCC9-4C17-B7DB-49A6D5D31D2E@microsoft.com...
> > You do not want to store an age in a table.  It will only be accurate for
> > less than 1 year.  The correct technique is to store the birth date in the
> > table and create a Public function to calculate the age where ever you 
> > need
> > to display it.  It can be on a form, in a report, or in a query.
> >
> > Here is an age calculation function
> >
> > Public Function Age(Bdate, DateToday) As Integer
> > ' Returns the Age in years between 2 dates
> > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> >
> >    If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
> >                Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
> >            Age = year(DateToday) - year(Bdate) - 1
> >    Else
> >            Age = year(DateToday) - year(Bdate)
> >    End If
> > End Function
> >
> > for a form, you would call it from the Control Source
> > =Age(Me.txtBDay, Date())
> >
> > for a report, it would be the same
> >
> > for a query, you would create a calculated field:
> >
> > Age: = Age([BIRTH_DATE], Date())
> 
> Why couldn't you simply use the DateDiff function? 
> 
> 
> 
0
Utf
5/25/2007 10:34:00 PM
Hi, Amy.

>> for a query, you would create a calculated field:
>>
>> Age: = Age([BIRTH_DATE], Date())
>
> Why couldn't you simply use the DateDiff function?

Because DateDiff() is only guaranteed to work correctly on the last day of 
the year.  Any other day of the year, the age returned will be incorrect if 
the birthdate is after today's date.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
info. 


0
69
5/25/2007 10:38:12 PM
On Fri, 25 May 2007 15:48:18 -0500, "Amy Blankenship"
<Amy_nospam@magnoliamultimedia.com> wrote:

>> Age: = Age([BIRTH_DATE], Date())
>
>Why couldn't you simply use the DateDiff function? 

It counts *year changes*, not full years. DateDiff("yyyy", #12/31/2006#,
#1/1/2007#) is equal to 1 - but that would make a one-day old baby appear to
be a year old.

             John W. Vinson [MVP]
0
John
5/25/2007 11:07:33 PM
Hi, Klatuu.

> For example, my birthday is today (it really is), so Had I done a datediff
> prior to today, it would return 64 while I was still 63.

Happy birthday!  Let me go get my fire extinguisher before we light the 
candles, just in case you can't blow them out in time.  :-)

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
info.


0
69
5/25/2007 11:56:23 PM
Reply:

Similar Artilces:

Do MFC ActiveX Controls have dual interfaces?
Do MFC ActiveX Controls have dual interfaces? Kurt ...

Sharing source code across multiple projects when source accesses theApp
I am working on a music plugin. The plugin is to be implemented for five different host applications. I have developed a lot of source code which I want to share accross each plugin project - so that I can avoid the hefty ordeal of having to copy paste source across each project upon changes. My problem is that various parts of the source code require access to the application theApp variable. Usually when I want to load custom dialogs - because you have to include the CWinApp derived class if you want to load your resources. I have routines in the app which load my dialogs so I d...

Macro to Update Charts Source Data
All, I have 50 sheets containing data... I want to create the same set of charts referencing each of these sheets. I was searching the groups and found the following link: http://www.peltiertech.com/Excel/Charts/ChgSrsFmla.html I tried to run each of the example macros, but with no success. Each time I get the error: Run-time error '1004': Unable to get the Formula property of the Series class When I click on Debug it takes me to the following line of code: mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, strFrom, strTo) Can anyone give me some insight as to why t...

Master/content pages: prob with passing a control to JS
Hi, my code: .... <script type="text/javascript"> var myfilter = myJSfunction(document.myform.ListBox1); </script> </form> When compiling the web page, because of the master/content, the Listbox1 control gets a different name/id apparantly. So how can I pass my listbox on as an argument of my JS procedure? thx for helping, J "Jokke" <Jokke007007@hotmail.com> wrote in message news:7839C9C3-037F-4550-B5BC-E6BA75D93A99@microsoft.com... > When compiling the web page, because of the master/content, the L...

IIF stmt in query
I would like to return different results for different conditions more than one field. here is my IIF stmt: IIF([transactiontbl.insttype]="A",”FMYHF0002”,IIF([transactiontbl.insttype]="B","FMYHF0003”,IIF([transactiontbl.insttype]="T",”FMYHF0005”,0))) or IIF([etype]=”R”,”FMYHF0004”,”FMYHF0006”) any help is greatly appreciated. Thanks You can AND the conditions, but it would be preferable to use a table to make the translations. iif( type = "A" AND country = "USA", "FMYHF002", iif( type = "A"...

Display full email address in addition to Nickname
People I don't know send me emails using their nickname "John", etc. I'd like to have be able to see their full email address displayed in my INBOX instead of just their nickname, so I know which emails to delete without opening or having to press options and the email address of the sender. If I add the column "email" nothing happens. Is there a way to do this? ...

Lookup Function in user form
I have a combo box (cboitem1) and a text box (txtprice1). I am using the code below to have txtprice1 fill with the corresponding price the worksheet: Private Sub Cboitem1_Change() If cboItem1 <> "" Then txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value, Sheets("Pizzas").Range("A1:B65536"), 2, False) End If End Sub It works perfectly when you enter an existing value in cboitem1. However if 'the user' accidently types a letter that is not the first letter of one of the existing items VB debugging pops up with error &qu...

Report of sales by month based on a cross tab query
Hi, I have a cross tab query that will provide sales by month for 2008. Right now it is sales for January08. As there are sales for future months, they will appear as well. I want to create a table that has all of the months listed out already. Right now, on the first day of the month, I go into the report and add the new month. I want it so that all month are listed which I have done. The problem is that when I run the report, I get an error that says "the Microsoft Jet does not recognize February as a valid field name. After today, there will be February data but no March ...

Which Controls to Build a Parent/Child Web Form
Hi there, I'm quite new to asp.net, but know exactly how to do this using classic ASP, but would like some pointers for aspnet2. I've got data being returned from an SP which looks like this: HeaderItem ChildItem A A1 A A2 A A3 B B1 etc I want to display the data like this A - This is the header record A1 A2 A3 B - Header 2 B1 etc. In classic ASP I'd write out my header row html when HeaderItem changes value and then write the Child Rows. As I've on...

ODBC Failure In JOIN Query ??? (BUT....)
I have a query that is simply a combination of 3 other queries (joined by 1 common field in each). Each of the 3 "sub queries" will execute fine, Individually.... But....that final query which simply combines them all will display an ODBC failure error. I'd always assumed that if each individual query ran okay....the query which combines them should not get such a data-access related error (since the core data is already being successfully accessed). Has anyone else experienced this and found a solution? Thanks very much -- Message posted via AccessMonster.com http://www.a...

OPOS Line Display Service Object
Hi, I am a newbie in OPOS. I am developing a service object for OPOS line display. When trying to use it through the MonroeCS CO control (OPOSLineDisplay.ocx, version OposCCOs-1_11_001), Open gives code 104 (=OPOS_E_NOSERVICE), and get_OpenResult() gives code 305 (=OPOS_OR_BADIF). What these return codes really means ? In Control Programmer's Guide (OLE for Retail POS, Release 1.6) this is said in page 18, item 5: CLIP --> .... (This ensures that the Service Object supports at least the minimum methods of a valid Service Object for the device class, before calling any of its methods...

addressing envelope from data input form
I don't know where to start to try this. I have the following fields that make up the entire address: Fname Lname Fname2 Lname 2 Address City, State Zip Is there any way to "push a button" on a form and have it print an envelope for that record? Thanks in advance for any help. -- Posted via a free Usenet account from http://www.teranews.com Have you thought of using an MS template as a starting point? http://office.microsoft.com/en-us/templates/CT101172481033.aspx Dave Eliot wrote: >I don't know where to start to try this. > >I have the following fields ...

Viewing xml created in .Net in a ASP form VB6
I have been trying to create an xml document in VB.Net for displaying on an existing page written in ASP. im having a type mismatch error in my code. When i view my XML i have the following included in the <binary> node. <Binary xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">DATA HERE </Binary My issue is how do i set the datatype of the the node using dotnet so that i can replicate the same node structure and use my existing ASP page. thanks in advance Colin Graham ...

LINQ related query
Hi I have a question regarding joining xml files on a key which is present in all file, following is my code docFTR is of type XDocument xdList is List(Of XDocument) Dim doc = From x In docFTR.Descendants("Data") From i In xdList.GetRange(0, 1) Join d In xdList(0).Descendants("Data") On x.Descendants("Guid").Value Equals d.Descendants("Guid").Value i need to programmatically replace xdList(0) with the current XDcoument which comes from "From i in xdList.getRange(0,2)" GetRange(0, could be 2 to 10) Any help is welcome...

Deleting Linked Source(s)
In Excel 2000, how do you delete a link Source(s)? I know you can 'clear contents' in each destination cell to achieve this, but is there a simpler way. Can be difficult to determine which cells are in fact destination cells. Thanks, Jim This may help you http://support.microsoft.com/default.aspx?scid=kb;en- us;214127 >-----Original Message----- >In Excel 2000, how do you delete a link Source(s)? I know >you can 'clear contents' in each destination cell to >achieve this, but is there a simpler way. Can be difficult >to determine which cells are in fac...

Runtime Control Addition
Hello everybody, I was searching the net for any resource that helps me on the subject of Adding Controls at runtime into a MFC application. I also wanted to handle the messages as well. Please help me on the subject with any tips or resources you know. Thank you so much. Shervin Hello Shervin, > Hello everybody, > > I was searching the net for any resource that helps me on the subject > of > Adding Controls at runtime into a MFC application. I also wanted to > handle > the messages as well. > Please help me on the subject with any tips or resources you know. >...

List Box no displaying added choices
I have several drop-down list boxes in a few forms that I am having trouble with. When I originally set up the database, I created the tables and entered a few items to be included in the drop down list in the lookup section of the table (design view). I then created the forms and inserted those fields. I have since added several new choices to the drop-down list of the table, and those new selections are available in the Table, but they are not available in the Form. The only items available in the drop down list on the Form are those choices that were originally there when I set up the fo...

Button Control Event: question
I have a small dialog that prints data it queries from a server to a listbox when a user presses a start button. What I'd like to do is to have the data continually print after the button is pressed until the user presses a stop button. So, I guess I would need to initiate some kind of continuous loop for the data querying/printing, but I'm unsure how to get the stop button to break the loop. If this even the best solution. Does anyone have any ideas of how I could accomplish this, or perhaps a better design idea? Thanks, Marcus Marcus wrote: > I have a small dialog that prints d...

How to display video in Outlook
emails containing video display the video as large blocks of text characters. How do I get Outlook to display video and play its accompanying audio? "lombo" <lombo@discussions.microsoft.com> wrote in message news:E4EF6106-000D-4710-81D5-3B6D2A0B19DF@microsoft.com... > emails containing video display the video as large blocks of text > characters. > How do I get Outlook to display video and play its accompanying audio? I suspect you're seeing the raw MIME encoding. This is a sign you have something interfering with Outlook's correct decoding of...

Creating a form or template
We scanned in an image of a post it note that has graphics on it. It is a jpg file. Can we somehow make this jpg file a form or template in Outlook? Ideally, we want to pull up a form that looks like the image of a post it note with the graphics in tact and be able to compose a message on the email post it note. How do you accomplish this? Thanks! ...

Adding BMPs to a form
I use Access 2003. I have a table set up with a field for pictures, this table sits behind a form to add BMPs, which display on the form and print on reports. In the past, when I have had to reinstall Access for any reason, the pictures instead of displaying fully in the form, only show as thumbnails with the file name. I have in the past resoved this by removing Microsoft Picture manager from MS Office. I don't know how I figured that out or why it works but it does. However, I had to reinstall Office yesterday, removed MS Pictrue Manager as usual and it's NOT working ...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...

Hyperlink in my Dialog control
I want my text in a dialog control to behave like a hyperlink in a web page. Is it possible and how(VC 6.0) Thanks I. Lesher http://www.codeproject.com/miscctrl/hyperlink.asp Here are some examples: http://www.codeguru.com/staticctrl/HyperlinkStatic.html http://www.codeproject.com/staticctrl/cmyhyperlink.asp http://msdn.microsoft.com/msdnmag/issues/01/01/c/default.aspx (look for CStaticLink) -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Lesher" <Lesher@triplebit.com> wrote in message news:uRDIoju#DHA.808@TK2MSFTNGP12.phx.gbl... | I want my text in a dialog contr...

Removing gray shading in protected forms (drop down box)
Hello- I am creating some contract templates for my company's sales force using Word 2003. I am using the forms toolbar to insert checkboxes and drop down boxes from which to select deliverable specifications. The problem is that when a user selects an option from the drop down box, the gray shading remains. I know the sales teams HATE the gray shading because they don't think it looks very good when sending to customers. Is there a way to disable the shading when an option is selected? Thanks. You can remove form field shading altogether using the corresponding b...

Microsoft Query #4
I receive message SQL0104 when changing a column heading in Microsoft Query while using the Client Access ODBC driver to connet to an iSeries 400 server. I have been informed by IBM that this is a problem with Microsoft's Query in that it incorrectly quotes the new Column Heading name before sending it to the ODBC Driver . Is anyone aware of a update or solution? Thanks Nick Thanks everyone, I just located the following Microsoft Knowledge Base Article 298955, I followed the procedures below, and the SQL0104 error went away. 1. Quit all Office programs.. 2. Click Start, and ...