I am working with an access table populated from an excel spreadsheet due to its source if a person has more than one line of data the fileds are left blank.
Example:
ID Name Date diagnosis
01 Smith, David 07/01/2004 sore throat
02 Strep Throat
03 Broken arm
For Smith, David he had 3 diagnosis but the field date and name are blank as they would have been the same as ID 01. I have written a query to populate the blank fields:
IIF([name] is null, dlookup("[name]", "tblDiagnosisUnder16final","[Id]=" & [id]-1),[name]) This works great for lines with only one blank, but in the above example for ID 03 the name field stays blank as it is looking at field 02 which is blank in the table.
What I need is a loop to keep moving up the table until it finds a field that <> Null. I assum I should write a function and palce it in the if statement above where the -1 is but I am strugling with comming up with a solution.
I am trying to generate letters from ACC2003 db into Word2003. Reason for Word is so client can edit letters if required. I have set up a maketable query to generate a temporary table in the Db and the mailmerge master document all formatted and pointed at the temporary table. I still need to get the automation running so that the letters merge and print on a command button. My immediate issue is that the address lines are intentionally spread across 5 fields Addr1, Addr2, Addr3, Town, Postcode. Not all fields in every record are full, and with the query it would be impossible to eliminate fields on the chance they might be empty. As a result my mailmerge has blank address lines which do not look professional. Can anyone point me to how I might solve this given my proposed use - if it was a report then I have a little code module that neatly eliminates blank lines but I cannot see how I could integrate this into the mailmerge process. Thanks for any help or pointers
I am creating a report that is organized by project. The detail lines are to list payments applied to the project. How can I skip the detail section (or print a single blank line) if there are no payments in the separate payment table that match the project ID? Is there a way to tell that there were no matching payments and format accordingly? I currently get multiple blank lines.
I have created a forum that all fields are unbound. On completing it is written to the table using a RunSQL command. This writes to the table with no problem but when it does so it is creating another entry with zero content. This is the save steps with open new form
Code: Private Sub SaveRecordAddNew_Click() Call PreSaveCheck If Me.saveCheck = False Then Exit Sub Else Call WriteToTable
I have a specific form, paper version, that I'd like to reproduce in an Access report. This form, which has 34 blank lines in the paper version, is used when people need to borrow some Tools or Equipment on a short period of time.
My report is made up in Access, but, if a person signs for, lets say, three different piece of Equipment, once it is sent to the printer, it prints three lines, which are the equipement signed for and it leave a big empty space before it reach the page footer.
What I need is something that will fill that empty space with blank lines. I found the following on the Net which meets my needs up to a point. What I mean is once it print the last line, it keeps on printing the last line until it meets the total count of 34 lines.
Here what I found on the Net: (note that I have a =count(*) into the Group Header)
Option Compare Database 'Use database order for string comparisons Option Explicit Global TotCount As Integer ' Call the SetCount() function from the group header section's ' OnPrint property using the syntax: =SetCount(Report)
[Code] ....
I submitted an attach file (blank_lines.gif) to show you what I end up with.
I have a WO Table and a related WO Procedures Table. I would like to add a specific # of blank lines/records between each record but that # will vary. So the report will show the WO Procedure Desc then a specified # of blank lines below it and then the next WO Procedures Desc then a specified # of blank lines below it etc.
I thought to add a field to the Proc tbl called #ofLines and whatever number was entered would be the # of blank lines to appear below the record on the report.
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried: 1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing! 2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
I have been running the same database on numerous systems for 5 years and the user clicks on a button and using this code;
DoCmd.OpenForm "Employee", acNormal
Simple !, this always opened this form blank, without applying filters, ready to accept a new employees details.
But now this no longer works, for no apparent reason? What happens is that it opens with the form fields filled with the first person in the Databases details instead of being blank.
The only thing that could have changed is that i converted the database to 2003?
When entering information into a blank form, I would like to be able to continue entering information to another additional blank form after my last entry. Is there a way to continue to a blank form after entering information into the previous blank form? I would just like to continue without having to close the entire form and then reopening another form.
Sipping branchReceiving BranchProduct #Qty to ship a c PR1 43 a d PR1 25 a k PR1 1 ....
Now I want to delete all lines that have same product code and shipping branch as a privous line. So the only max amounts for each product and each shipping branch will remain.
Hi. (I already ask how to delete rows but it looks too hard to make so I invent new idea) How I can add lines (INSERT INTO) into table so that Access does not add lines which already are in the table??
Is it possible to create a message box with two lines?
Also I want to make a reference to the valuein a text box in the message box.
This is my code at present:
If MsgBox("ARE YOU SURE YOU WANT TO DELETE THE WHOLE EXERCISE?", vbYesNo + vbQuestion, "Warning") = vbYes Then CurrentDb.Execute "DELETE FROM ExerciseNametbl WHERE ExerciseName = " & Chr(34) & Me.ExerciseName & Chr(34), dbFailOnError Me.ExerciseName.Requery Else Exit Sub End If
What I would like is:
"Are you sure you want to delete (The value of comboExerciseName)"
Hi, sorry my english, but I will try to explain what I try to do. In my database I have a form, where is a listbox. In this listbox are names of documents. Problem is that the name of each document is so long (two or three lines on monitor) so it is not possible to fit in, because listbox has always only one line for each document. It is possible to set up listbox to get more lines for each item ? I will be very happy if somebody help me with it. Thank you very much. Jirka
How would I go about getting vertical lines as column seperators down the entire page. The problem I run into is every time the row size changes there's a break in the column dividers. Someone suggested using a function in the On Print event for the report, but there is no OnPrint field in the event tab of the reports properties. If anyone ever did anything like this before, please let me know.
Is there any way of making a comments box (memo) have the functionality to press return and start on a fresh line as it all seems to run into one 'indecipherable blob'
In a query, how do I do a count of the number of lines that appear in the query? Not the number of records, because the query itself is grouped. The results are in order, highest to lowest, and I want to see just the top 50.
Hi, I have the following code to display the message as in the picture Private Sub Staff_Number_NotInList(NewData As String, Response As Integer) MsgBox "CREW MEMBER NOT IN LIST - Double clic on this field to add a Crew Member" Response = acDataErrContinue End Sub What I wouldlike to do is get the message written on 2 lines instead of 1!!!
i'm no noob, but i'm also not the expert, what i'm trying to do is this, i'm taking multiple string out of an recordset and putting them in one big text box but the problem is that after every record there has to be an enter in the text box So for example:
record 1 = -bla bla bla -tja tja tja
record 2 = -waa waa
my result in the text box must be: -bla bla bla -tja tja tja -waa waa
and not (like it is now): -bla bla bla -tja tja tja -waa waa
who has a clue? chr(11) & chr(13) do not work, i activated "on enter go to next line"
I have a csv file, I am able to import it using the vba commande docmd.TransferText action. But the first three lines are made up of headers. I do not want to import these.
xxxxx, xxxxx, Field1, Field2, Fileld3,
Is there a way that I skip out the first 3 lines of the text file? ie only import from row 4 onwards?
Please Help
My current transfer method also creates a table of errors for the first three lines (as they do not match the data type spoecified by the target table). It also creates a whole lot of null fields in the first three rows of the table. I am running a loop for a whole lot of csv files so you can imagine I'm generating a lot of error import table. If I could import only rows 4 onwards there would be peace in the universe!
Does anyone know how to make a report have lines in it like excel can do when you print out. My boss likes to read this data with lines seperating stuff. Thanks.
