Hi - thanks for the read ! I have seen many examples of this but I cannot get it to work for me!
I hoped that if I plugged in my data someone could shed some light!
I have a table that is recording shipment data for an order. An order ships, we need to capture that in three fields with an autonumber ID, like this:
GRID OrderNo(LI Number) ShipDate(Date) GRNo(Short Text)
1 51056 7/13/2021 SG20210609001
2 51056 7/13/2021...
MC42015
Last Activity: Sep 14 '21, 03:02 PM
Joined: Sep 13 '18
Location:
-
Create query columns from similar rows
-
Trouble utilizing nested IIf Statement
Hello - and thank you for reading my question!
I have experience with IIf and Nested IIf, but I am completely stuck on this one. I have written in 15 versions of syntax and cannot get it to work.
Here's what I need - I have an query that I need to analyze four fields of each record. One of these is fields always contains a value, the others vary. They are all number fields, but the numbers are not for expressions - they are just... -
twinnyfo - I have made GREAT progress! All of your tips to loop me through Access land and simplify this data into a concise database! So happy I achieved what you and I worked on!
However, I found some missing info that I am now trying to fix.....Grr.
My new problem is data type mismatch in union query.
In my invoice table from the client - they include all fields on every record for that invoice, the sales rep number,name... -
I was hesitant about the table names because I wanted a simple upload from the client database every week. I do not like the spaces! I have to research smarter imports, I spend too much time.....
Salesreps is a separate table
OK! I think I'm getting somewhere. I pasted your code exactly as below got Runtime 91-obj variables or With block variable not set at Set OIL. I'm not sure what it's looking for. Queries are receiving...Last edited by twinnyfo; Apr 24 '20, 03:40 PM.Leave a comment:
-
Sorry, my upload fails when I try to sendLeave a comment:
-
It is a subform on this main form. My old version has on-click the subform and send data to unbound fields, but since I was having so much trouble I tried to eliminate 10 unbound fields in this form. Should I build that back in?
The main form is unbound with a combo box for Sales Associate ID: when selected two subforms with query record source load sales data for that ID with master/child link. This works fine.
The on click...Leave a comment:
-
I've gone back to test it - I'm getting 2450 - can't find the form SlmnChooser at & [Forms]![SlmnChooser]![sfSLMNOIL]![CURRENCYCODE]...Code:Private Sub Command29_Click() Dim MyOI As DAO.Database Dim OIL As QueryDef Dim sSalesman As String Dim sSubject As String Dim Where As Variant Set MyOI = CurrentDb() On Error Resume NextLeave a comment:
-
Maybe the code tests I've done - changing/eliminated some syntax is throwing it to the limit? Is it normal to keep deleting and restarting ?Leave a comment:
-
Yes! It was happening after compiling the code! I'm at a loss!Leave a comment:
-
I tried - but it still only half works. What I do is run weekly commission statements and customer statements that are uploaded as text from a clients system that doesn't link to the contacts/leads. They have a table of open customer invoices, and a table of sales associate open invoices.
These are used for two different things.
I used to upload every invoice and every payment to create the statements on my own - but decided I would...Leave a comment:
-
I'm in Not Responding Rut
Hi - I spent the week starting a fresh new database with about 10 back end tables, 20 or so basic queries, 3 union queries and 6 forms.
This Open Invoices environment is simple stuff with very little coding.
The back and front end are totaling 2,800-7,400 kb. Not very big.
I am testing now to go live Monday. I am the only user. I've had to change some formatting here and there but the big problem is it doesn't run!!!... -
OK - sorry for the confusion! You are not seeing that because i am manually going into the table and typing it in.
This is the reason for my question.
Two tables make an Order - tblOrder CHID - date, order number, order date, etc; and tblOrderDetails relates to CHID with qty,line item,price,etc.
My process is an order is recorded with CHID and I monitor it there until it becomes an Invoice IID. Two tables make an Invoice, exact...Leave a comment:
-
Append Query with added intel
Hi! can you help?
I am looking to write a parameter in an append query to assign a value to one of the fields I leave blank.
I need to replace CHID with IID
Example - I have an NB (not billed)order, CHID 10, with three lines, description, price, etc:
CHID 10,1,Tomato Sauce,2.00,EA
CHID 10,2,Spaghetti, 1.00,EA
CHID 10,3,Chianti,7. 99,EA
When my corporate office generates an invoice for this, the invoice... -
Combine selections from listbox in VBA
Hi - I've adopted some code from font stuff to be able to select multiple rows in a list box to send to a query.
I'm using an Autonumber ID field to identify the records I want from the list.
Can anyone help me with my run-error 3075 on STMT2? Syntax Error(missing operator) in query expression '[qdfMULTI.CustID] in (100',102')'.
Those are the correct items, but when I check my query, it is returning all.
... -
Thank you twinnyfo - you gave me a lot of clarity here!
My frustration has lowered!!
So, yes, these will always be 2-pages.
I'm going to play with it some more which may cause more questions.
Wish me luck! I want these live by Feb 5........Leave a comment:
-
Report print
Hi - I haven't built a report in years and may have lost the basics....
I am creating a report for a single contract I send to a single customer.
Portrait .25" margins all around so I have 8" width and 10.5" height. I am having trouble printing in order the way I need, and am looking for instruction how to set that up in VBA.
Record source is a query with parameters.
It has these sections:
Page... -
thank you for your guidance!
This qry has two parameters that I can work around (if that is the problem!)
One is the date - to pull the records I want
The other is for testing - to pull fake account that e-mails to me feature -
...Code:SELECT tblSTMT.STID, qrySendEM.CustID, qrySendEM.ACCT, qrySendEM.APCEmail, qrySendEM.STMTAP, tblSTMT.STDATE,Leave a comment:
-
Send email with attachments using VBA
I have been working in copy and paste for years and would love to automate the bi-monthly invoice summary e-mails that I send to my customers!
My Access 2013 database creates summaries for customers exported to an excel spreadsheet.
With the help of Ron De Bruin code, I was able to have excel split the worksheet into separate workbooks.
Back in Access, I have a parameter query that will pull a list of these... -
access 2013 - runtime 3061
I have been trying to build an OnClick in Access VBA to send separate outlook emails with corresponding attachments from an Access query. I have found a bit of old code that I am trying to adapt.
This is for monthly invoice statements.
I have a parameter in the query to pull the exact attachments I need by date.
My query records start with a unique(Autonumb er) id, and includes the e-mail address and the path... -
Access SQL input records without a join
Hi! Thanks for checking out my dilemma!
I know I have done this before (in another db) but I can't recall exactly.
What I have are two sources: a query of open invoices that creates a customer statement and a table of customer contacts.
When I export to excel, I want to take the contacts with me - but I do not want to duplicate my invoice records for all the contacts.
The query fields(invoice) are ID, ACCT, INVNO,INVDATE,...
No activity results to display
Leave a comment: