Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DlookUp in a text box (2000)

    Why, if I select July 7, 2003 in the startup form of the attached mdb(see also attached picture), I cannot get the Text28 textbox to display the result of the lookup function in its Control Source?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    1. The field to be looked up in the query is named cc, not coloriz.
    2. Date values must be surrounded with # characters to avoid them being interpreted as calculations.
    3. Date values must be in US date format mm/dd/yyyy.

    Try the following expression:

    =DLookUp("cc","query1","[giorno] = #" & Format(Forms!Pac!giorno;"mm/dd/yyyy") & "#")

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    Thank you so much Hans,
    I've noticed that just the following suffices:
    =DLookUp("cc","query1","[giorno] = #" & [Forms]![Pac]![giorno] & "#")
    without the need to format it the American way.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    Hi Giorgio,

    Whether the expression works without formatting the date with US format depends on the exact contents of the giorno field, and on the date format in use on your system. On my system, the default date format is dd-mm-yyyy. Today, for example, is 02-10-2003. In SQL, this would be interpreted as February 10, 2003 instead of October 2, 2003.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    As you can see if you open Table1, the Giorno field is formatted as Medium Date dd-mmm-yy. I guess I'm able to do without formatting the date because I've selected English(US) in Regional Settings of the Control Panel(see Dams.jpg).
    Could you please tell me why I cannot enter the date in the Annotazioni table of the attached mdb as shown on the attached Annot.jpg?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    Giorgio,

    Change the Input Mask property of the MeseAnno field from >L<LL-00;;_ to >L<LL-00;0;_. The extra zero specifies that Access keeps the dash - that is part of the mask. Without it, Access removes the dash before trying to store the value, so Jul-03 becomes Jul03, and that is not recognized as a valid date.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    Thanks again Hans,
    Please consider the attached mdb. How can I link the startup Pac Form with the Annotazioni subform so that the subform displays the note relevant to the month and year selected in the Pac form's Giorno combo box?
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    Giorgio,

    You can do this, but it takes a bit of work. I have attached the modified database. You want to link the main form and subform on month and year, so you must make these available. I created a query qryAnnotazioni that returns all fields from the Annotazioni table, plus calculated fields for month and year:

    SELECT Annotazioni.MeseAnno, Annotazioni.Annota, Month([MeseAnno]) AS Mese, Year([MeseAnno]) AS Anno
    FROM Annotazioni;

    I set the record source of the Annotazioni subform to this query. You also need the month and year on the main form. I created text boxes Mese and Anno for this. Their control source is an expression: =Month([Giorno]) and =Year([Giorno]). In the database as posted, I left them visible so that you can see how they work, but in the final version, you should hide them. The Link Master Fields and Link Child Fields properties of the Annotazioni subform object on the mainform are both set to Anno,Mese so that they are linked on year and month.
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/megashout.gif border=0 alt=megashout width=33 height=17> <big> You are simply awesome <img src=/S/yep.gif border=0 alt=yep width=15 height=15> and a <img src=/S/gent.gif border=0 alt=gent width=17 height=25>
    </big> <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/angel.gif border=0 alt=angel width=15 height=21> <img src=/S/king.gif border=0 alt=king width=21 height=22> <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>Hans

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    Hans,
    Your solution allows me to update the Annota field in the Annotazionin table but how can I make it so that the MeseAnno field in the Annotazioni table is updated too when I type a date containing a new month, say, e.g., 01-Sep-0301-Sep-03, in the Giorno combo box?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    To test for a new date, set the Limit To List property of the Giorno combo box to Yes. You can put code in the On Not In List event of the combo box to create a new record in Table1, and to test whether a new record should be added to Annotazioni. Here is the code for this event:

    Private Sub Giorno_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim lngCount As Long
    Dim strGiorno As String

    ' Ask user whether new record should be added
    If MsgBox("You entered a new date. Do you want to create a new record?", _
    vbQuestion + vbYesNo) = vbYes Then
    ' Add record to Table1
    strSQL = "INSERT INTO Table1 ( Giorno ) VALUES (#" & _
    Format(NewData, "mm/dd/yyyy") & "#)"
    CurrentDb.Execute strSQL
    ' Test for record in Annotazioni
    strGiorno = "#" & Format(DateSerial(Year(NewData), Month(NewData), 1), _
    "mm/dd/yyyy") & "#"
    lngCount = DCount("*", "Annotazioni", "MeseAnno=" & strGiorno)
    If lngCount = 0 Then
    ' Add record to Annotazioni
    strSQL = "INSERT INTO Annotazioni ( MeseAnno ) VALUES ( " & strGiorno & " )"
    CurrentDb.Execute strSQL
    End If
    ' Tell Access that record has been added
    Response = acDataErrAdded
    Else
    ' Undo new date
    Giorno.Undo
    ' Don't display standard error message
    Response = acDataErrContinue
    End If
    End Sub

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    You did an awesome job again. <span style="background-color: #FFFF00; color: #000000; font-weight: bold"> <font color=red>Thanks Hans</font color=red> </span hi> .
    Please open the Delta mask. I've tried formatting the bottom text boxes (pz1, pz2, pz3, pz4) so that they display only one decimal but why can I achieve that only by selecting Fixed as format and not, e.g., General number or even leaving the Format box in the properties sheet blank as long as I type 1 in the Decimal Places box?
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    Giorgio,

    The Decimal Places setting is ignored if you set the Format property to "General Number" (or leave it blank). "General Number" means that the number is displayed "as is", without number formatting.

    If you want a fixed number of decimals, you must use Fixed or Standard. The difference is that Standard uses thousands separators and Fixed doesn't.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DlookUp in a text box (2000)

    Hi again Hans,
    I've created a new report called <big>RPAdati </big> in the attached mdb but...it just disappeared! If I try and create a new report with the same name I get a message saying, "The name you entered already exists for another object of the same type in this database". Do you know of a way to make the hidden report reappear?
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DlookUp in a text box (2000)

    I fear that you're the victim of some kind of database corruption. I don't see any way of retrieving the vanished report. I don't think it is present any more, but there is a ghost listing of the object in the hidden system table MSysObjects. If you save a new report as RPAdati, and reply Yes to the prompt to replace the existing one, you'll be rid of the ghost name.

    Although I didn't notice any other problems with the database, it might be wise to create a new blank database and import all database objects from Dams.mdb into it. If you have startup options in Dams.mdb, you'll have to re-create them manually in the new database.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •