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:
Can anyone help please?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.eLike [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.How do I stop this happening? Thanks in advance.
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?
For some strange reason, a replica db is being created along side the original. I don't believe it's a back up since the name back_up usually appears in the file name. I've seen it happen once when the proper mdb file was opened and then noticed the new db was created (always called db1.mdb). It's a permanent file mdb file and not the record locking icon. I remember over the summer someone posted a similar problem but I haven't been able to locate that thread. Any suggestions?
Although I have been using access for a year or two (self taught) I have only recently noticed that the form toolbox (the one with combo boxes and buttons etc on it) appears to have a library of procedure calls for calendars and all sorts of things sitting there waiting to be used. Is there a list somewhere of what all this lot does? I have been playing with a couple of date pickers but I don't particularly want to go through all of the rest to see what they do or if they would be useful.
Hi Does anyone know how to delete the extra blank fields you get at the end of a query. I am converting the query to a handheld database with a field limit and the extra fields push my field count over the limit. i have tried just 'deleting' them but they reapear when I reopen the query. they definitely count as fields because if I delete some real fields i can do the conversion. i dont want to alter the structure of the original database as it has been in use some time and works well. any ideas advice Thanks supateach
Hi again I am still having problems with too many fields in my query so it will not convert to a handheld database. I have tried making a test query (with the wizard)from the same table and sure enough there are extra fields at the end, which do not come from the table, which reappear after they have been deleted, when you reopen the query. I have attatched the query to illustrate that I have deleted the fields and then they reappear. It does appear to be impossible to get rid of them, in which case I shall have to decrease the number of 'real' fields to get below the max the handheld can convert. Any advice gratefully received Thanks supateach
I have a table t1 which contain many records, the field look like:
id f1 f2 f3 ---fn
Id is the primary key I have some records which have different id but exact the same other fields. like id f1 f2 f3 ---fn 87 1 4 6 ---9 12 1 4 6 ---9 18 1 4 6 ---9 116 1 4 6 ---9 1287 1 4 6 ---9 98787 1 4 6 ---9
for those records, I only want to keep one record (any one) and remove all others. How can I do that?
I have the following expression, which needs to be wrapped by some extra logic:IIf([NetTotal]<>0,IIf([NetTotal]-[InvoicesRaised]>0,30,[InvoicesRaised]/[NetTotal]*30),0)What I need to do is, if the above expression comes to less than 0, then the control box needs to display 0, otherwise it should display the value of the expression.Now, I know I can do this by IIf(expression<0, 0, expression). However, this adds a lot of code in there and the expression is put in twice. Is there an alternative way of doing this ?
Is it possible to have an extra field that is added to the calculated weeks left? Such as what if I want 2 extra weeks instead of the 12?
I know this is probally an advance question! Thanks in advance!!!
This is the awesome example from sbenj69:
Expr1: 12-(datediff("ww",[join date], date())
What I want to do is add another column which will have an additional number. The additional number would just be added to the 12- part of the code. So if I have a field with 2 in it, the 12- would become: 14-(datediff("ww",[join date], date())
Is this truly possible or is it something that can't be done in access
I have 120 tables, each with the same name except 2 identifying characters at the end eg pc_dist_ab, pc_dist_al Each table currentnly has 3 columns. I would like to be able to add 2 additonal columns to each table with one query and was hoping an alter table query where the table name matches pattern would have worked but evidently not.
Is there a way to build some sort of dynamic query to add extra columns to these different tables at the same time?
To save another post I guess Once this is done I would then like to create 1 main table by creating a new table and appending all the files together- again I would prefer to be able to run this once.
I'd appreciate any help/thoughts as to whether this can be done?:confused: Thanks
We recently converted to Access 2010 after using 2000. This problem has suddenly appeared. It doesn't happen on every report but there is one in particular right now and I can't figure it out.The first page of the report is complete but it prints a second page containing only the page header and footer, the rest blank. There are only 2 detail records on the first page and plenty of blank space.
It's not the issue of the page overflowing onto extra pages because the page size bleeds beyond the margins. Everything is safely inside the set margins, and in fact as a test I brought the page width down to 4 inches with .5" margins and it still prints a second page.
Copying a column from one record to another. Code is:
Code: Set fld = Records.Fields("violationLocalOrdinanceNumber") If Len(fld & "") > 0 Then rst!LOR_NB = Nz(Records!violationLocalOrdinanceNumber, "") Debug.Print ("LOR NB:" & rst!LOR_NB & "." & " len:" & Len(Nz(Records!violationLocalOrdinanceNumber, "")) & " len new:" & Len(rst!LOR_NB)) End If
Some of the output I'm getting is:
LOR NB:8.08(5) . len:7 len new:25 LOR NB:7.08(5)(a) . len:10 len new:25 LOR NB:7.08(5)(a) . len:10 len new:25 LOR NB:7.08(5)(A) . len:10 len new:25 LOR NB:7.08(5)(A) . len:10 len new:25 LOR NB:7.08(5)(a) . len:10 len new:25 LOR NB:8.08(5) . len:7 len new:25
No matter what the original string length is, something is adding extra spaces and forcing it to 25 in the new record. This is the only field I've been able to identify with this issue. Column definition is char(25), no indexes or anything special that I can tell.
Tried adding a left() function call after the assignment but that didn't work either.
I need to add HolDte and make it also use HolidayDate as it's criteria.
PHP Code:                strSQL = "INSERT INTO tblHour (WorkDate,Hours,HolDay,EmployeeID) " _ & " VALUES  (#" & Me.HolidayDate & "#," & Me.txtHrs & ",True," & Me.EmployeeID & ")"Â
When I open my access 2007 database from the switchboard, the form that it opens up to is a parent form with a subform embedded in it. The subform is linked by the 'org name' and by 'year'.
I am finding that when I open the parent form,there is always an additional empty record in the subform, alongside the record which has data in it.
I am not sure why it is doing this, especially as when you enter another record, this empty form vanishes. Its almost acting as the default form?
I have also noticed that when I go to another form which is displaying specific data linked to the 'org name' and 'year' it also has an extra record
However, the weird thing is it does not show up in the table. And again, once you go to a new organisation and input some data, and then go back to the other organisation,the additional record has gone?
Is there a simple property setting whereas each time you open the form, it always opens up to where you essentially left off, no empty record?
I have a sub-report and the last row is highlight, however, there seems to be extra spacing after the last row. I removed all report/page header/footer so all what's left is the Detail section but I still have this white space after the last row.
I have a form that looks and prints exactly as I want it to, except for two little things. These things are extra pages. They don't appear on the print preview, but they always show up on the printer and on the pdf file when I email it. One page is blank and the last page has "Page 1 of 1" at the bottom right corner of the page. I would always limiting the printing to one page.
Customer Table > Customer ID (pk) 1 Issues Table > Customer ID (FK) Many Complaints Table > Customer ID (FK) Many
Each table has Customer ID as you can see, I have linked the Customer ID From Issues & Complaints table to the Customer ID field to the Customer Table using a 1 to Many relationship.
Am creating a form on the customers table so an admin can add a customer issue against the customer this is working fine, The trouble is when I link the Complaints table as well. its asking for subdatasheets.
I have a continuous subform with allow additions set to false. To make a new record I have used some update vba to create the record direct in the underlying query, then requery the form and the partly created record appears. The user then adds a quantity and some text. The subform still appears without the new record line.....However if I click the button again to create a second new record I end up getting an extra 2 lines.
One is a duplicate of my previous one and a new blank record. These do not actually appear in the underlying table and the subform looks ok. However this extra record confuses the end user and I want to avoid it. Refresh or shift f9 does not eleviate the problem. Sometimes I even get two "current record" pointers.
I'm intending to make two extra fields open up if a particular value is selected. Just as a simple example:
Field: Type of Item Possible Values: Alcohol, Beverage, Cutlery
So if either a beverage or alcohol is selected, I would like a new field to open up. For both Alcohol and Beverage, I would like "Content of bottle in mLs" to open up. For Alcohol, I would also like "Percentage Alcohol content" to also open up.
So basically, if a specific value is selected, I would like extra fields to come up. I'm not sure if there's any simple way to do it, or whether it requires VBA coding, but either way if it is possible I would like it to be done.
1) How do you make an input mask enter from right to left? For example, if post codes can either be 4 or 5 digits long, I put 90000 (requiring at LEAST 4 digits) but if you start entering data, it starts from the 9. Would it work if I did 00009? 2) Is there any way to make a form go through a progression of screens rather than all the input fields being on the one page?
Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.
I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.
I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?
