Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Unanswered: entering the now() parameter when a field is empty?

    Hi,
    I am very new to access and am trying to build a data base from the ground up.
    I work with children and need to record the amount of time they are being cared for.
    In my query I have two columns. One called PlaceStart and the other PlacetEnd.
    When all the data is filled in for the relevant tables I can run the query easily with the column criteria set at >=[Enter Start Date _ _/_ _/_ _ ] for Place Start and <=[Enter End Date _ _/_ _/_ _ ] in the PlacetEnd Column.

    My problem is that for children still in care the data is not yet populated with an end date and I would like the query to assume that the end date is either NOW() or the date I type as the EndDate in the query.
    If this makes sense to someone I could really like a suggestion.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    As you say you are new to Access, I'll try to be as specific as I can, forgive me if I come off as condescending, let me know if I'm not specific enough.

    Go to your query, and view it in SQL view; you should see a bunch of text, this is the core of a query and is what the query builder is building for you. I assume you have some sort of statement that will look something like this:
    Code:
    SELECT ..., [tblName].[fldName], ...
    FROM tblName
    WHERE (([tblName].[fldName] >= [Enter Start Date _ _/_ _/_ _ ]) AND ([tblName].[fldName] <= [Enter End Date _ _/_ _/_ _ ]));
    Or something near to that, where "tblName" will be the name of your table, and "fldName" will be the name of your field with the date in question. If you have other fields being selected, that's fine, just leave them alone as it doesn't sound like they're being a problem.

    Then try changing the part of the SELECT statement with the date field in question to read something like:
    Code:
    SELECT ..., IIF(IsNull([tblName].[fldName]), [Enter End Date _ _/_ _/_ _ ], [tblName].[fldName]) AS [fldName], ...
    What this will do is first check to see if the field has been entered for a particular row. If it hasn't, then it will take the entered End Date. If it has, then it will take that date/time for that row. Then when you apply your WHERE criteria (where you enter a Start and End date), it should include them.

    I say should because I'm not sure if that'll work or not. I usually prefer to build the queries dynamically with form entry, but that is a bit more than I think what you're looking for. But give that a try and see if it gets you where you want.

    By the way, since you're getting into database design, I would suggest having a look of relational database design, good reading for anyone getting into this sort of thing.

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Still Trying Here

    I really do not mind condescending at all. I am to new to be offended and really appreciate that you were able to take time to help.
    I changed the SQL as you mentioned and ran the query but it would not pick up the records with blank end date fields. I am pasting the SQL that was created below in case you can see something I have done wrong.

    SELECT T_Placements.PlacementNumber, T_Placements.PlaceStart, IIF(IsNull([T_Placements].[PlacetEnd]), Now(), [T_Placements].[PlacetEnd]) AS [PlacetEnd], T_Placements.RefAcceptDate, [RefAcceptDate]+56 AS 56Days, T_Placements.ClientID, T_EnterClientDemographics.ClientName, T_EnterClientDemographics.ClientSurName, [ClientName]+" "+[ClientSurName] AS ClntFullName, T_EnterClientDemographics.ClientDoB, DateDiff("yyyy",[ClientDoB],Now()) AS Age, T_Placements.CgID, T_EnterCareGiverDemographics.[CgName(s)], T_EnterCareGiverDemographics.CgSurName, [CgName(s)]+" "+[CgSurName] AS CgrFullName, T_EnterClientDemographics.RefOrg, T_EnterClientDemographics.RefSite, T_EnterClientDemographics.BdNtCgrPmnt, [PlacetEnd]-[PlaceStart] AS Bednights, ([PlacetEnd]-[PlaceStart])*[BdNtCgrPmnt] AS CgPmnt, T_EnterClientDemographics.PocketMoney
    FROM T_EnterClientDemographics INNER JOIN (T_EnterCareGiverDemographics INNER JOIN T_Placements ON T_EnterCareGiverDemographics.CgID = T_Placements.CgID) ON T_EnterClientDemographics.ClientID = T_Placements.ClientID
    WHERE (((T_Placements.PlaceStart)>=[Enter Start Date _ _/_ _/_ _ ]) AND ((T_Placements.PlacetEnd)<=[Enter End Date _ _/_ _/_ _ ]))
    ORDER BY T_EnterCareGiverDemographics.CgSurName;

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What I do in the query designer for situations like this is put in this expression for the EndDate column (replace EndDate with your field name):

    EDate: iif(isnull([EndDate]),#01/01/2050#,[EndDate])

    Then I apply the <= criteria under that column.

    (NOTE: Format of what is entered for the date MUST be the same as the #01/01/2050# in the above example! Change it to #1/1/2050# or whatever format the date is going to be entered as in the prompt or it won't work. This should be the same format as the actual field format unless you've applied special formatting in the table design for this field.) (ie. if they entered 5/1/2009 instead of 05/01/2009 when prompted, the above example expression probably won't work - you would need to change it to #1/1/2050# - but then 05/01/2009 would not work if that was entered at the prompt.)

    Formatting of the date field is key along with how it's entered in the prompt (they all must be the same format!!) I've even had to use expressions like this (try this if the above doesn't work):

    EDate: iif(isnull([EndDate]),#01/01/2050#,Format([EndDate], "mm/dd/yyyy"))
    or
    EDate: iif(isnull([EndDate]),#1/1/2050#,Format([EndDate], "m/d/yyyy"))
    Last edited by pkstormy; 08-22-09 at 03:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Almost There?

    Hi pkstormy,
    I Used your formula to create a completely new Column as I could not get it to work in the criteria to the original.
    I used this to build the column EDate: IIf(IsNull([PlacetEnd]),#1/01/2050#,Format([PlacetEnd],"mm/dd/yy")) That part worked but...

    I need the 01/01/2050 date to be todays date and I tried replacing it with Date() to read EDate: IIf(IsNull([PlacetEnd]),Date(),Format([PlacetEnd],"mm/dd/yy"))

    Unfortunately it no longer located the records with the empty cells where I needed it to mimick todays date.

    I thought we had beaten it several times over the last hours and its still giving me headaches.
    Any more thoughts?

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Have you tried adding an OR criteria to your WHERE statement? Something like:

    Code:
    SELECT 
      [T_Placements].[PlacementNumber], 
      [T_Placements].[PlaceStart], 
      [T_Placements].[PlacetEnd], 
      [T_Placements].[RefAcceptDate], 
      [RefAcceptDate]+56 AS [56Days], 
      [T_Placements].[ClientID], 
      [T_EnterClientDemographics].[ClientName], 
      [T_EnterClientDemographics].[ClientSurName], 
      [ClientName]+" "+[ClientSurName] AS [ClntFullName], 
      [T_EnterClientDemographics].[ClientDoB], 
      [DateDiff("yyyy",[ClientDoB],Now())] AS [Age], 
      [T_Placements].[CgID], 
      [T_EnterCareGiverDemographics].[CgName(s)], 
      [T_EnterCareGiverDemographics].[CgSurName], 
      [CgName(s)]+" "+[CgSurName] AS [CgrFullName], 
      [T_EnterClientDemographics].[RefOrg], 
      [T_EnterClientDemographics].[RefSite], 
      [T_EnterClientDemographics].[BdNtCgrPmnt], 
      [PlacetEnd]-[PlaceStart] AS [Bednights], 
      ([PlacetEnd]-[PlaceStart])*[BdNtCgrPmnt] AS [CgPmnt], 
      [T_EnterClientDemographics].[PocketMoney]
    FROM [T_EnterClientDemographics] 
      INNER JOIN ([T_EnterCareGiverDemographics] 
        INNER JOIN [T_Placements] 
          ON [T_EnterCareGiverDemographics].[CgID] = [T_Placements].[CgID]) 
        ON [T_EnterClientDemographics].[ClientID] = [T_Placements].[ClientID]
    WHERE 
      (([T_Placements].[PlaceStart] >= [Enter Start Date _ _/_ _/_ _ ]) 
        AND ([T_Placements].[PlacetEnd] <= [Enter End Date _ _/_ _/_ _ ])) 
      OR (IsNull([T_Placements].[PlacetEnd]))
    ORDER BY [T_EnterCareGiverDemographics].[CgSurName];
    EDIT: Made SQL more readable
    Last edited by nckdryr; 08-22-09 at 16:51.
    Me.Geek = True

  7. #7
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Inserting Dates

    Hi Nckdryr,
    Thanks and this could be on the right track. It is certainly populating the columns and pulling the reports in that do not have the [PlacetEnd] date entered. It is not yet converting that empty cell to todays date. I keep trying to insert a criteria in design view at the bottom of the [PlacetEnd] column similar to IIf(IsNull([PlacetEnd]),Date())
    And the program keeps rejecting it
    Any more thoughts?

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Well, if you're able to pull the right data, then you're on the right track I think. Perhaps you can get what you're after by doing some formatting at the report level. Maybe try doing something like the following in the OnFormat event of the Detail section of the report:

    If IsNull(me.PlacetEnd) then me.PlacetEnd = date()
    Me.Geek = True

Posting Permissions

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