I am making this harder than it has to be i'm sure. I had a previous post of auto-texting a field, well why can't I just take two fields I have, such as the ID field which is an autonumber field and combine that with a type field that I have which is D.
If I could merge this in a query, wouldn't it give me the results as D01, D02, etc?
I couldn't find anything helpful online, one side said do the following.
SELECT receivables2.type+receivables2.ID
FROM receivables2;
Only I get errors.
I also tried this.
SELECT merge(receivables2.type; receivables2.ID);
FROM receivables2;
Hi Gurus, Here's my situation. I have three columns in Access: Attribute Name, DateValue, CharValue. I wish to create a make table query which does the following: If Attribute Name = 'approved_date' then take the value from DateValue Column and populate it in a New table 'new'. but if the Attribute Name = 'description' the take the value from CharValue column and populate it in the same table 'new' in a different column. Please help, how to achieve this.
I have a table which has a column and some of them are filled and some are blank. I want to fill the blank ones with the previous values, how do I do this. Thanks for any help..
Is there a way to hide a column in a query that does not have any data in it?
I have tables that are populated with YES/NO boxes and I only want to bring back the information for fields that are YES. Currently, the queries run and bring back all fields.
Each query will only have 1 result as they are limited by the primary key.
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:
Private Sub Text66_DblClick(Cancel As Integer) Dim Units Dim UnitsCache Dim PackagesCache Dim F4x5Cache Dim F5x7Cache Dim E8x10Cache Dim E11x14Cache Dim S16x21Cache Dim T21x24Cache Dim WalletsCache Dim T4x5 Dim T5x7 Dim T8x10 Dim T11x14 Dim T16x21 Dim T21x24 Dim TWallets
If [4x5] = Null Then T4x5 = 1 Else T4x5 = [4x5] End If
If [5x7] = Null Then T5x7 = 1 Else T5x7 = [5x7] End If
If [8x10] = Null Then T8x10 = 1 Else T8x10 = [8x10] End If
If [11x14] = Null Then T11x14 = 1 Else T11x14 = [11x14] End If
If [16x21] = Null Then T16x21 = 1 Else T16x21 = [16x21] End If
If [21x24] = Null Then T21x24 = 1 Else T12x24 = [21x24] End If
If [Wallets] = Null Then TWallets = 1 Else TWallets = [Wallets] End If
RerunPackages: PackagesCache = "x" If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "F" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 [T21x24Cache] = [T21x24Cache] - 1 Text58 = Text58 + 439 GoTo FoundOne End If
If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "E" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 [S16x21Cache] = [S16x21Cache] - 1 Text58 = Text58 + 305 GoTo FoundOne End If
If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then PackagesCache = "D" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 3 [E11x14Cache] = [E11x14Cache] - 1 Text58 = Text58 + 236 GoTo FoundOne End If
If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "C" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 Text58 = Text58 + 169 GoTo FoundOne End If
If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then PackagesCache = "B" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 3 Text58 = Text58 + 127 GoTo FoundOne End If
If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then PackagesCache = "A" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 2 Text58 = Text58 + 89 GoTo FoundOne End If
FoundOne: If PackagesCache <> "x" Then If Text56 = " " Then Text56 = PackagesCache GoTo RerunPackages ElseIf Text54 = " " Then Text54 = PackagesCache GoTo RerunPackages ElseIf Text52 = " " Then Text52 = PackagesCache GoTo RerunPackages End If End If
UnitsCache = (T8x10 + T5x7 2 + T4x5 4) - UnitsCache Do If F4x5Cache < 4 Or UnitsCache = 0 Then GoTo Skip4x5 End If F4x5Cache = F4x5Cache - 4 UnitsCache = UnitsCache - 1 Loop Skip4x5: Do If F5x7Cache < 2 Or UnitsCache = 0 Then GoTo Skip5x7 End If F5x7Cache = F5x7Cache - 2 UnitsCache = UnitsCache - 1 Loop Skip5x7: Do If E8x10Cache < 1 Or UnitsCache = 0 Then GoTo Skip8x10 End If E8x10Cache = E8x10Cache - 1 UnitsCache = UnitsCache - 1 Loop Skip8x10:
Text42 = 0 Redo: If WalletsCache >= 5 Then Text42 = [Text42] + 40 WalletsCache = [WalletsCache] - 5 GoTo Redo End If If WalletsCache = 4 Then Text42 = [Text42] + 37 ElseIf WalletsCache = 3 Then Text42 = [Text42] + 33 ElseIf WalletsCache = 2 Then Text42 = [Text42] + 28 ElseIf WalletsCache = 1 Then Text42 = [Text42] + 18 End If
I'm in need of some help again. I have two tables, Table1 and Table2. I have one field in each table that I want to have the same data. I don't want blanks to be merged, but I want all the data to be the same
for example
Table1 --------- 1
3
5
Table2 --------- 1 2
4
6
I would like both tables to be merged to show
Table1 ------- 1 2 3 4 5 6
Table2 ------ 1 2 3 4 5 6
But I want to just update both tables and not create anything new.
I have a database on a machine with office xp. I store the database in C:Database. I can run a query then link the results of that query to a template document I have setup via mail merge. The document then opens and fills out the fields based on the query results. I transferred the database onto another machine with office 2003 and put it in exactly the same directory c:database. and when i try to link the query to word, word just comes up saying it cant find the source. I have gone around various machines at work and the merge runs fine on all machines with office xp and not with 2003.
I have four tables, each containing name of one family member. Dad, Mom, son, and other. One of the four called us, and in many instances, they ask that we send a letter to them. Sometimes, they say, send the letter to Mom, or send it to dad, or sis. We are creating an automated output query to print one of two letters, to go to the appropriate person. Depending on which person is selected as the one to receive mail, we need to merge out into Word that person's data, which may be in any of the four tables, but is the only one bearing an indicator (checkmark, etc).
Don't know how to word the query to look thru the data on that family (from four tables) and select the indicated one, output the address info.
I have a form on which users select fields to include in a query and can select their parameters. The query itself is then created using an SQL statement in the VBA behind the form.
I want to be able to use the records which this query selects through mail merge. With a standard query, this is easy because from within Word I can choose the query through the mail merge settings. However, since the query doesn't exist in this case (it is created every time it's run using VBA) I can't figure out how to do it.
Ideally, I want the form to come up during the mail merge process so that the user can specify which records to use, but I can't select a form as a mail merge source.
I wonder if anyone can help I have been using Access Queries in order to create merge letters in Word for years and now we have upgraded to Office 2003 everything seems to be going wrong. This is what I do:
1. Open a query in design view enter the relevant text in the appropriate field in order to select the records I want from the table, then I run it and the correct entries are there.
2. Open a standard letter in Word which was created and has been linked to the above mentioned query years ago.
This is where the problem starts in the past the letter opens up and the correct info is displayed so I go ahead and print. Now what is happening rather then linking to the query it is either displaying the last record that was used or nothing at all and in the record number box it says 0 when I know that there are several selected in the query.
I have tried re doing the merge setup and nothing happens the only way round it I have found is to merge it with the table rather then the query then using the Mail Merge Recipients Box you have to go through and reselect the records you want which is a pain as there are 945 records in the table.
I am trying to put a button on a form that will run a mail merge to populate a Word (2007) document with information from the single record in the form. I would think this would be easy to do, but after working on it, and searching the internet, I'm finding it is not. Normally I would simply use an Access Report to do this, but the document is about 7 pages long (a contract), and people want to have the ability to modify the result.
What I have done so far:
-I've created a form that displays a single record from my Customers table -I've created a query that pulls the Customer fields I need, and is filtered on the Customer ID that is active in the form. When I open this query by itself, it pulls back the one record I want. -I then opened both the form and query, and then run through the External Data - Mail Merge wizard.
Problems I've run into:
1) Initially when I tried to select my query in the Mail Merge Wizard, it wouldn't show. I then found tips to prompt for the data connection type when the file is opened, and to set it to MS Access with DDE. This allowed me to see the query, and seemed to work OK when I added the fields I needed to the Word document. But then when I tried to rerun the Word doc, I would get a connection error. If I run through the connection setup process again, it seems to work, but again when I rerun it later, I get the connection error. For simplicity I have put the Access DB and Word document on my desktop. Eventually these will go on the network.
At this point I have a button on my form, and it successfully opens the Word document, but the "merged" fields just show the record that I had saved the document with, so there is no true merging going on. If I open the Access DB Form & query and then open the Word document directly, I get the error "Word could not re-establish a DDE connection to Microsoft Access to complete the current task".
it's possible to use a union query as a mail merge? I haven't found anything that says I can't do it, but I'm not getting my merge to complete, and when I switch to a plain query (and not changing anything else) my merge is successful, so I'm thinking there might be a limitation.
way to merge various fields from a table into just one using a query.The purpose will be for easier copying and pasting in to an email.So we have a table to stores information such as Site Contact, Address 1, Address 2, Address 3, Postcode etc.
I want to be able to run a query that will put the address in to just one box, either in the query or on a report then it's much easier to just highlight the full address and copy and paste it into an email. Rather than copying each field individually.
Because I have more than 3 queries, it is really hard for me to manage. Therefore, I was thinking of having 3 crosstab queries to show in one single query. This way it will save my time from moving back and forth.
I use an access db that stores our customer info on a mssql backend. I need to be able to create a mail merge based on a customers renewal date. Now I do know how to set parameters in a query but is there a way I can use a access report to enter a month and day they are set to renew then have it create the mail merge based on their info?
The Layout is fairly simple just a renewal notice with what they have on their plan.
I have my word template set up such that some data is copied from the open form into the document and some data is taken from a table which is created from a query based on the content of the form (the record number). This works well HOWEVER (there's always a however isn't there :rolleyes: ) where two users click on the button at exactly the same time or within a very short space of time the query only runs once and either the database crashes (bad) or the document is created for the second person with the wrong data (very bad).
How can I make it so that only one person can run the query at a time (ideally build in a delay in processing so that the query will run for them when the first person has finished...failing that a message to say please try again later)? They will always be working with a different record.
I'm a bit stuck as to what to search for in the forum for an answer to this so haven't really searched.....sorry :(
I am trying to create VBA code (I have very little experience of VBA) to recreate a parameter query mail merge, which Access 2013 cannot achieve with the controls provided.I think that I need to assign a variable to the value of a control on my form then assign that variable to the relevant criteria section of my query.
Code: DIM RefNo as String DIM IDNo as String RefNo = Me.[Reference Number].Value IDNo = Me.Text582.Value DoCmd.OpenQuery SORTER How do you paste into criteria?
From there I would like to do a Word Mail Merge by having VBA open a document in Word, then activate the Merge to a New Document instruction.
