Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1

    Answered: Using field with date not working in report

    This is what I'm want to us in a field on my report:
    =IIf([First_Communion_Parish]<>"St. Joseph" Or [First_Communion_Date]<>#5/3/2015#,[First_Name]+" "+[Last_Name]+[First_Communion_Date]+" @ "+[First_Communion_Parish],[First_Name]+" "+[Last_Name])

    But I get a #Type! error when the first part is true. But if I take out the [First_Communion_Date] before the @, it works fine. So any ideas why it isn't working? Thanks!

  2. Best Answer
    Posted by healdem

    "the symbol to add strings (to concatenate) together is & NOT +. Access will generally make allowances and handle + as a &, but if the adjacent column is numeric it throws a type error. A date value IS stored as a number but represented as a date."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the symbol to add strings (to concatenate) together is & NOT +. Access will generally make allowances and handle + as a &, but if the adjacent column is numeric it throws a type error. A date value IS stored as a number but represented as a date.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Fixed it, thanks! I'm not sure why I got in the habit of using + instead of &. Do you think I should go back and change everything? It would be a bit of a project, but not too bad...

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its working OK then leave well alone
    if you have problems fix it
    moving forward do it correctly.....
    ..a bit like naming tables, column, a control, variables and so on.
    there's little point in unpicking stuff that is working just because some one declares this or that is the right way. but moving forward do so.
    but be aware that this coudl be the reason for a problem going forward. Many self styled 'professionals' sneer at Access and VBA but it makes a vary good job of trying to work out what develiopers meant to say as opposed to to actually said. it will successfully handle such problems. but be aware it is a potentail sourcer of problem and if you get odd errors going forward it could be this sort of thing.

    your code may well work fine, but could break if in future your string / text variables / columns contain numbers

    in exactly the same way as using invalid symbols or reserved words in table or column names most times |Access will work out what you want but when it goes wrong it goes wrong badly, often difficult to track down errors becuase mentally you are certain a block of code is good because its run for years .
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by KarenElissa View Post
    Fixed it, thanks! I'm not sure why I got in the habit of using + instead of &. Do you think I should go back and change everything? It would be a bit of a project, but not too bad...
    probably because you are used to the Java / C world where such mangling of the language is not just encouraged it approved of .
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    probably because you are used to the Java / C world where such mangling of the language is not just encouraged it approved of .
    No, I don't think that is it, I've played around with Java a tiny bit, but it has been awhile and I was certainly never good with it.

    And thanks for the advice. I want to do things "right", but since this is a side project, I also don't want to get bogged down in details either, it is interesting trying to find that balance. But I think it is better than the database I'm replacing and it can certainly do more and do things better, so even if it isn't totally "correct", I think we are better off than we were at least.

Posting Permissions

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