I have a search form that passes 4 parameterers to a query.
In the query builder it reads:
Like [FORMS].[frmCourseSearch].[txtCourse] & "*"
>=[FORMS].[frmCourseSearch].[txtStartDate]
<=[FORMS].[frmCourseSearch].[txtEndDate]
[FORMS].[frmCourseSearch].[txtCourseID]
This works fine. But I also want it to search on only 1 or 2 parameters and set the rest to Null so I have used this statement for each one.
Like [FORMS].[frmCourseSearch].[txtCourse] & "*" OR Like [FORMS].[frmCourseSearch].[txtCourse] & "*" Is Null
This again works ok and I can search on any parameter. However, every time I enter data into the search form and run the query it repeats the query string in the query builder. i.e
Like [FORMS].[frmCourseSearch].[txtCourse] & "*"
Like [FORMS].[frmCourseSearch].[txtCourse] & "*"
Like [FORMS].[frmCourseSearch].[txtCourse] & "*"
etc...
It does this with all 4 parameters and it's starting to look very messy. The SQL view is the same with a new repeated line added each time I run the query.
I have been modifying an existing report that was formatting and printing fine but since I modified it (adding some text boxes and moving some controls around), it is now adding a blank page.
For example, If the report was normally 2 pages long with page numbers, it is inserting a blank page without page number between page 1 and page 2......
how I get rid of the lines appearing in a report? I've set everything to transparent but the group header and subsequent records in the subreport all have separating lines.
Managed to export a table to csv file. The problem I now have is I need to add the following Text at the start of the file:-
H,LKJ85485524,DE
and the new last line :- T,whatever the record count is,2
Whether this info is added before or after export is not important.
I have even resorted to using VBA to create an Excel spreadsheet and saving to csv.
Thought this had cracked it however I had surplus commas at head and footer where the original csv file had additional columns (ok manually delete these comas) ,then found that the end of each csv line was missing a final comma that is needed by the recipient of the file.
When a query returns no records, the form appears blank. How can I make the form appear eventhough there are no records to show prior to inputting data.
I made an database with all addresses. When persons live in an appartment, then the field "busnummer" will be filled in with a number. When they live not in an appartment, the field "busnummer" is empty.
In a query, I made an expression and filled the expressien in field (in the query):
Expr3: IIf([Busnummer]>0;"bus [Busnummer]";"")
When "Busnummer" is greater then 0 THEN write the text "bus" and the value of the field right behind, else leave the field empty.
The result of the expression above is: "bus [Busnummer]"
I have already tried the following: Expr3: IIf([Busnummer]>0;[Busnummer];"")
The result of the expression is the number I filled in in the database. Now I want to have the result: bus and the number I filled in... :)
I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused: I currently have 2 queries based on 2 separate tables.
Ops_Log_996_Query SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate FROM 996_Table WHERE ((([996_Table].hiredate)=Date()));
Ops_Log_SQTU_Query SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate FROM SQTU_Table WHERE (((SQTU_Table.hiredate)=Date()));
When run separately the first query returns 2 results and the other 1 result - fine so far. I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -
SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate FROM Ops_Log_996_Query, Ops_Log_SQTU_Query GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;
This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:
Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.
Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));
What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".
i have this problem that is bugging the crud out of me: sql="UPDATE bedrifter SET pr=" & Request.Form("pr") & ",totalindexedpages=" & Request.Form("tip") & ",totalinboundlinks=" & Request.Form("til") & ",description='" & Request.Form("dsc") & "' WHERE created='" & Request.Form("ts") & "'"
conn.Open connStr conn.Execute(sql) conn.close() Set conn = nothing
when i run this code it updates the correct record (line in my access db) but then it also adds a new line with only that info in the update query. why is it doing this? when i update using the ID instead of using the timestamp in the WHERE clause it works fine. really frustrated...
Good Evening Everybody, I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:
ID: Auto Number Area: Text Equipment:Text EquipSrlNo:Text EquipLocalNo:Text MOPNo:Number DateDone:Date/Time MOPPeriodicity:Text
I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???
SQL SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity FROM [Main Data] WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));
Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.
Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
In Expression Builder and other areas of Access where the fields of a query show, my fields are not showing anymore for any of my queries. Does anyone have a solution for this?
I'm not sure if this is a dumb question or not but is it possible for me to have a button on a form that will open the Query Builder?
I have a form that has 3 buttons on it - 1 to access records, 1 to access reports and another which I would like to open the Query Builder when clicked. The database will be used by beginners so I've hidden the design view button and also the database window which appears at startup. All that appears when I open my database is the 3-button form which is what I want.
I thought maybe I could save a blank query and then create a macro to open that whenever the button was clicked and stipulate in the macro to change the view to design view but that didn't work.
Thanks to any help or advice anyone can give and sorry if it turns out to be a really stupid simple solution I've missed (I'm good at that!)
I have to three fields in my query wizard, lets call them A and B and C. The condition for the query is either A or B is Null and C is Null. Im not sure how to do this though becuase A, B and C are different fields. I wrote
Code: Is Null
.
under the criteria of A and B and C, but this specifies that A,b and C is Null, how can I make it so that C must be Null and either A or B or Both.
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
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.
I have created a query which is based on 2 tables.I now want to use the query to update content as needed in one of the tablesWhen I try to do this it says, Operation must use an updateable query..I used the update feature but it still says must be created in updateable query.
I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:
(refer attachment)
Is there any way to write this query so that I can get output as below:
The purpose of my database is to keep a record of training activities undertaken by staff. There is therefore a multiple form which displays, for a specified worker, the list of all the required activities. Each line has a button which opens another form to allow an activity to be started (together with start date and other info). The main tables are Worker, Activities, and a junction table Worker2Activities. On closing the second form and refreshing the first one, the started activity is correctly recorded, but on as many lines as there are staff on the course. To make matters worse, these also appear when i then select another worker (and if i create another worker, the number of lines goes up by one for all of them). The Worker2Activities table correctly show one new record created
I have two tables that need to be exported on one .txt file.
One table has the header record. And the 2nd table has the detail record.
My issue is as follows: 1. Need to create one .txt file 2. The header record has one set of export specs and the detail record has a different set of export specs. 3. The first line is the header record, and 2nd line is the detail record.
Example below: H Abraham Armenta January Cake 00123 D Father Saturday 02536 00123
Both tables have a common control number - in this case represented by the 00123. I just cannot figure out how to accomplish this..
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#)) & "*"
