Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2006
    Posts
    65

    Unanswered: Cant reference ASCII chars

    Hi,

    one of my db's uses ascii characters when combining some fields to insert a carriage return each field is joined. Its an address field comprised of name, address and postcode:

    [HomePTitle]+Chr(13)+Chr(10)+[Homeaddress]+Chr(13)+Chr(10)+[HomePostCode]

    so the output is
    [HomePTitle]
    [Homeaddress]
    [HomePostCode]

    ready for labels and letterheads.

    ive moved this db to the users machine and that query no longer works. It was written on access 2002 and my user uses access 2002 so theres no conflict there. I had to remove missing a missing reference from the vb on my users machine- i dont know which one but noit sure that has anything to do with it as the problem is with the sql not the vb...

    any ideas of what's going on here?

    greg

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dubs
    that query no longer works.
    Hi greg

    Fancy expanding on the above a bit?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2006
    Posts
    65
    Hi Pootle,

    yes essentially im using a query to piece together three fields into, as the fields need to be ready for address labels and letters i need the carriage return after name and address so the field will display:

    [HomePTitle]
    [Homeaddress]
    [HomePostCode]

    rather than

    [HomePTitle][Homeaddress][HomePostCode]

    Both machines are using access 2002 yet when i try and run the query on my users i get an error message:

    'access doesn't recognise expression ..........[HomePTitle]+Chr(13)+Chr(10)+[Homeaddress]+Chr(13)+Chr(10)+[HomePostCode]'

    or something to that affect (my user is away to do so i can go back and check at the mo)

    so im stumped as to why the query works on one machine but not another.

    greg

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What happens if you remove the Chr(10) and 13?
    Is this an mde?

    Also:
    Do you understand the difference between + and & concatenation characters in Access?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The problem is your references. Chr is a built in VBA function. Whenever you have any problems with Left, Right, etc. and you have just tried the DB on another system, it is almost always references.

    This problem also arises when there are differences in the versions on the different machines. Even if you don't see Missing Reference you can have the same problem. To fix it, go into references, uncheck a reference then exit. Then go back into references and recheck the reference you unchecked. This forces Access to evaluate all the references and reset them for whatever version is on the existing machine. This should clear up your issue.

    Ultimately you will need to figure out which missing reference you got rid of. Otherwise another part of your database won't work (unless the reference is not being used).

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ... and in most cases, if a reference is bad, NOTHING outside the Access Application object works, including all of VBA.

    Left, Right, Mid, Chr, InStr, Format, Iif, Val ... just to name a few

    Also, as pootle pointed out, you should be using & instead of + when assembling a string. + is a numeric "sum" of values where & is an "assembly" of pieces.

    4 + 72 = 76
    4 & 72 = 472

    Finally, it's a good idea to put Chr(13) & Chr(10) in parenthesis:
    ... & (Chr(13) & Chr(10)) & ...

    tc

  7. #7
    Join Date
    May 2006
    Posts
    65
    Nice one, now my user is back I think ill copy another version over and do what you said. is there anywhere which breakdowns what the references offer?

    greg

  8. #8
    Join Date
    May 2006
    Posts
    65
    yeah a bit weird that one, looking at my other apps i always use & when trying to concentate strings but for some reason used + here..ill have a look,. thanks for your help

    greg

  9. #9
    Join Date
    May 2006
    Posts
    65
    oh and yes its an mde...didnt think there was any differences between the mde and mdb once it was compiled...my it manager is trying to tell me that using mde's is bad practice

    greg

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The difference between + and & when concatenating strings is that:

    'Something' + NULL = NULL
    'Something' & NULL = 'Something'

    So - if any of your address fields were NULL you would get a null return. If you make judicious use of + and & though you can come up with something e.g.
    Code:
    ([HomePTitle]+Chr(13)+Chr(10)) & ([Homeaddress]+Chr(13)+Chr(10)) & [HomePostCode]
    I think the brackets are right there. The logic is - if one of the fields is missing then the Chr(13) and (10) do not get printed so you don't end up with blank lines. Using + and & like that can be really handy for building up these types of strings.

    Using an MDE is not bad practice (dear lord - we are hearing about some real nonsense from people's bosses at the moment). In fact - if you were to poll members here I suspect the answers would mostly be of the opinion that to not use an mde is bad practice. It does of course depend on the specifics of a situation. However - check the service packs. When using mdes the client machine must have the same or lower version of Access and the same or lower service pack if the versions match.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2006
    Posts
    65
    Excellent Info....im learning a lot here

    would you say i should always use ade's if putting the apps on clients' machines or are mde's ok? i dont see a problem but my IT manager banned me from rolling out mdb's (fine with me dont want anyone messing with my code) but now says i shouldn't be using mde's either

    im essentially a dba experienced in user maintenance and data analysis but now am having to use a db which is so badly written it doesnt have any primary keys- which means ive had to build my own apps to get the data to work properly....sadly ive had no formal training and our network manager is completely incompetant- saying that i think ive found my new favourite site!!!

    thanks again

    greg

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - to correct my previous post - substitute 'higher' for 'lower' when discussing versions

    An ade is a compiled, 'secured' version of an Access Project.
    An mde is a compiled, 'secured' version of an Access Database (although it may contain no tables or data of course).

    They are not simply interchangable. Dubs - were you the guy whose IT Manager thought that a 3MB FE was far too large to distribute?

    Why not an mde? What alternative does he\ she suggest?

    I have no idea what you mean by user maintenance but it sounds jolly cheeky.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by pootle flump
    The difference between + and & when concatenating strings is that:

    'Something' + NULL = NULL
    'Something' & NULL = 'Something'

    So - if any of your address fields were NULL you would get a null return. If you make judicious use of + and & though you can come up with something e.g.
    Code:
    ([HomePTitle]+Chr(13)+Chr(10)) & ([Homeaddress]+Chr(13)+Chr(10)) & [HomePostCode]
    As pootle says, both + and & can be used as concatenators. Search the vb help for ‘+ operator’ as it gives the explanation of how it deals with different argument types.

    The subtlety of the point made by pootle made have gone unnoticed so I thought I’d re-iterate the implecation of his example. The point of pootle’s example is that it is a great way of creating addresses with no blank lines (rather than a horrible list of IF statements).

    Also, I've found vbCrLf causes me less problems in vb than Chr(13)/Chr(10).

    Chris

  14. #14
    Join Date
    May 2006
    Posts
    65
    yeah i caught that

    this is it- i designed all my apps as mde's- out of the four that are being rolled out two are data entry and two are reference apps...IT man says anything non server side should be ade...i suspect because he doesn't like me he is just critising for the sake of it because i cant see why it should matter that much...the fe's are being rolled out to stop lock-file conflicts, the tables are secured, the code is compiled and the fe is locked- cant see the issue.

    Yes i was and i think this could be part of the issue just explained, also couple that with the fact that our network guy doesn't actually know what he's doing and you have the brick wall which is my IT dept.

    he suggests using ade's all over- not convinced, means exporting all my elements from my mde's into ade's and then expecting all the code/expressions/functions etc to work....not likely

    user maintanance? in my last job we had over 100 and it was all about ensuring they put the data in in the right way so i could extract it easily...couldn't think of a better wy to describe it

    greg

  15. #15
    Join Date
    May 2006
    Posts
    65
    Chris,

    yes i got that, was just having a play with it- i think i have a new standard to stick to- it def makes sense this way otherwise if i didnt have a contact name id get a carriage return followed by a carriage return at the start of my address...

    ill also look into vb, im just not used to using vb at the query level but ill def look into this.

    greg

Posting Permissions

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