so the output is
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...
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:
Both machines are using access 2002 yet when i try and run the query on my users i get an error message:
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).
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.
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!!!
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).
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
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.