Results 1 to 9 of 9

Thread: Query Help!

  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Query Help!

    Help, I have a query linked to a form that allows the user to select criteria for a report creation.
    I have all the tick boxes and combo boxes working as they should but when it comes to selecting years I am using the following
    Year(DateAdd("yyyy",-3,Date()))

    This works when its type into the query (based on last 3 years), but I need to have the user select whether they would like to use it by means of a tick box. So if they tick 3 years box then it inserts the code into the query. I had an idea where I could link the tick box to a text box that either displays the above code or nothing at all, then link the query to the display box. The first part works but when the query links to it I get the following message:

    "The expression is typed incorrect or i too complex"

    Please help! I just need the typed code above to be inserted into the query or have the number inserted into the query such as :
    Month(DateAdd("",)" & "xxxxx year" & ", Date))

    Again that errors. Any help would be much appreciated.

    Thanks,

    Neil

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the full SQL statement of the query?
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    SELECT TBL_Wills.ID, TBL_Wills.[Will date], TBL_Wills.Dead, TBL_Wills.MailshotInclude, TBL_Wills.StaffRelative, TBL_Wills.Certainty, TBL_Wills.NRB, TBL_Wills.OG, TBL_Wills.ExecutorOther, TBL_Wills.ExecutorPalmers, TBL_Wills.ExecutorBoth, Year([Will Date]) AS Expr2, TBL_Wills.[Codicil Date], TBL_Wills.Surname, TBL_Wills.Forename, TBL_Wills.Initials, TBL_Wills.Title, TBL_Wills.Address1, TBL_Wills.Address2, TBL_Wills.Address3, TBL_Wills.Address4, TBL_Wills.Postcode, TBL_Wills.[Will date 2], TBL_Wills.[Will date notes], TBL_Wills.[Codicil Date 2], TBL_Wills.[Codicil Notes], TBL_Wills.[Executor name], TBL_Wills.[Executor Details], TBL_Wills.[Old Will Revoked], TBL_Wills.[Old Will Revoked 2], TBL_Wills.Notes, TBL_Wills.Benfleet, TBL_Wills.MichaelThomas, TBL_Wills.LargeWill, TBL_Wills.[Will taken by], TBL_Wills.[Date Will taken], TBL_Wills.[Reason Will taken], TBL_Wills.Comments, TBL_Wills.EvolutionCase, TBL_Wills.[Client No in evolution], TBL_Wills.Mailshot, TBL_Wills.Response, TBL_Wills.Location, TBL_Wills.DeadComments, TBL_Wills.Inheritance, Year([Codicil Date]) AS Expr1, TBL_Wills.Email
    FROM TBL_Wills
    WHERE (((TBL_Wills.[Will date])=[Forms]![LetterInputMailshot]![Will1]) AND ((TBL_Wills.Dead)=[Forms]![LetterInputMailshot]![TickDead]) AND ((TBL_Wills.MailshotInclude)=[Forms]![LetterInputMailshot]![TickMailshot]) AND ((TBL_Wills.StaffRelative)=[Forms]![LetterInputMailshot]![TickStaff]) AND ((TBL_Wills.Certainty)=[Forms]![LetterInputMailshot]![TickCertainty]) AND ((TBL_Wills.NRB)=[Forms]![LetterInputMailshot]![TickNRB]) AND ((TBL_Wills.OG)=[Forms]![LetterInputMailshot]![TickOG]) AND ((TBL_Wills.ExecutorOther)=[Forms]![LetterInputMailshot]![TickExecOther]) AND ((TBL_Wills.ExecutorPalmers)=[Forms]![LetterInputMailshot]![TickExecPalmers]) AND ((TBL_Wills.ExecutorBoth)=[Forms]![LetterInputMailshot]![TickExecBoth]));

  4. #4
    Join Date
    Mar 2004
    Posts
    287
    actually I believe the easiest thing is going to be to link the unbound field to say Will1, then make the criteria of the query linked to Will1 field, so if tick is enabled (eg: ticked) then Will1 shows 'Year(DateAdd("yyyy",-3,Date()))' and if its unticked then it equals ""
    So my question is how can I add the Year(DateAdd("yyyy",-3,Date())) to say a variant such as below, as the " signs do not allow it to be read as a field and it calculates instead

    Dim varTick As Variant
    varTick = Year(DateAdd("yyyy", -3, Date))

    If I place in " brackets then it reads the 2nd " as the end of the line?

    Thanks.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or take the value from the combo box as your feed
    your combo could have to columns one hidden, one visible eg 0: add nothing, 1: add year and so on

    Year(DateAdd("yyyy",mycombobox.value,Date()))

    personally I think it may be smarter to use say months, that gives you the option to use say 6 months or whatever.

    the hidden column is the value you want to subtract /add fromt he date, the other column is the human text
    make certain you always set a value in the combo box
    eg set to a default or Zero. you'd nee to do that in the forms on current event .. set to say zero, or perhaps set a default value on the combo. unless your users can add a different value then use a list box instead.. that will make it easier for you to code
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    didn't work, states it contains too many complicated elements.
    Here is the query code:

    Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will3],Date()))

    And the value of [Will3] is "-3"
    Last edited by NeilMansell; 07-30-10 at 10:17.

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    right i got it working with ticked boxes, but it doesnt work if the tick is not ticked (so to speak).

    So if Will1 is ticked then fine, query runs but if its unticked "" then doesn't run.
    If I leave 0 value then the value woudl come out at

    Year(DateAdd("yyyy",0,Date()))

    which is all dates, surely?

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    UPDATE:

    I now have it working although I want to add another date range (1,3,4,5,10,15,20,25,30)
    like WillDate. Here is the query SQL code:

    Code:
    SELECT TBL_Wills.ID, TBL_Wills.[Will date], Year([Will Date]) AS Expr2, TBL_Wills.[Codicil Date], Year([Codicil Date]) AS Expr1, TBL_Wills.Dead, TBL_Wills.MailshotInclude, TBL_Wills.StaffRelative, TBL_Wills.Certainty, TBL_Wills.NRB, TBL_Wills.OG, TBL_Wills.ExecutorOther, TBL_Wills.ExecutorPalmers, TBL_Wills.ExecutorBoth, TBL_Wills.Surname, TBL_Wills.Forename, TBL_Wills.Initials, TBL_Wills.Title, TBL_Wills.Address1, TBL_Wills.Address2, TBL_Wills.Address3, TBL_Wills.Address4, TBL_Wills.Postcode, TBL_Wills.[Will date 2], TBL_Wills.[Will date notes], TBL_Wills.[Codicil Date 2], TBL_Wills.[Codicil Notes], TBL_Wills.[Executor name], TBL_Wills.[Executor Details], TBL_Wills.[Old Will Revoked], TBL_Wills.[Old Will Revoked 2], TBL_Wills.Notes, TBL_Wills.Benfleet, TBL_Wills.MichaelThomas, TBL_Wills.LargeWill, TBL_Wills.[Will taken by], TBL_Wills.[Date Will taken], TBL_Wills.[Reason Will taken], TBL_Wills.Comments, TBL_Wills.EvolutionCase, TBL_Wills.[Client No in evolution], TBL_Wills.Mailshot, TBL_Wills.Response, TBL_Wills.Location, TBL_Wills.DeadComments, TBL_Wills.Inheritance, TBL_Wills.Email
    FROM TBL_Wills
    WHERE (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will1],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil1],Date()))) AND ((TBL_Wills.Dead)=[Forms]![LetterInputMailshot]![TickDead]) AND ((TBL_Wills.MailshotInclude)=[Forms]![LetterInputMailshot]![TickMailshot]) AND ((TBL_Wills.StaffRelative)=[Forms]![LetterInputMailshot]![TickStaff]) AND ((TBL_Wills.Certainty)=[Forms]![LetterInputMailshot]![TickCertainty]) AND ((TBL_Wills.NRB)=[Forms]![LetterInputMailshot]![TickNRB]) AND ((TBL_Wills.OG)=[Forms]![LetterInputMailshot]![TickOG]) AND ((TBL_Wills.ExecutorOther)=[Forms]![LetterInputMailshot]![TickExecOther]) AND ((TBL_Wills.ExecutorPalmers)=[Forms]![LetterInputMailshot]![TickExecPalmers]) AND ((TBL_Wills.ExecutorBoth)=[Forms]![LetterInputMailshot]![TickExecBoth])) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will3],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil3],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will4],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil4],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will5],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil5],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will10],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil10],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will15],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil15],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will20],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil20],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will25],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil25],Date())))) OR (((Year([Will Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Will30],Date()))) AND ((Year([Codicil Date]))=Year(DateAdd("yyyy",[Forms]![LetterInputMailshot]![Codicil30],Date()))));
    Any idea's?

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    Nevermind, all sorted, it appears it was placing AND commands inbetween, where they should have been OR.

    Thanks for all your help, much appreciated.

Posting Permissions

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