Breaking Up "IF" Statements
Jun 30, 2006
How would I break up "IF" statements? As you can see below, I'm trying to pull several IF statements in a formula, but it's giving me a "Undefined function 'IF' in expression" error.
I can't quite get it working to view the SQL code, so I'm having to paste the straight forumla from the Design code. This is very close to the code I was using in excel.
Accrual Days: IF([Ifs Inv To Date]="1/1/1900",0,IF([Ifs Inv From Date]>[Aging Date],0,IF([Ifs Inv To Date]<[Performance Date],[Period of Performance Days],([Performance Date]-[Ifs Inv To Date]))))
View Replies
ADVERTISEMENT
Jan 18, 2007
I want to take a report which covers sales figures and e-mail to those people that have had sales in the period. I only want each person to get just their records, not the whole report. Any ideas how I can do it automatically, starting with a query of records or a report ? ?
I know how mto e-mail a standard report using a macro !!
View 1 Replies
View Related
Mar 23, 2006
Hello all,
I need your help! Iam breaking my head for a day almost to solve this problem.
This is a timesheet application in MS Access in which I have a MS Access table with the following columns:
1. PersonID
2. SkillID
3. EntryDate
4. MON
5. TUE
6. WED
7. THU
8. FRI
The work hours for a person is entered once a week, at the end of the week. The person's SkillID can change but not in the same week. For ex a person scheduled as a Welder has to work as Welder for that week.
So, I need to find now all the PersonIDs with more than one SkillID in a week and flag them as errors.
Pls check the attached image. The first entry with EntryDate as "06-May-05" and Person_Code as "MK0259" repeating with 3 different Skill Codes. Then this is a problem which I want to hight light. Hope I explained clearly.
Can someone help please? Desparate!
Thanks in adv.
sgmuser!
View 2 Replies
View Related
Nov 3, 2006
Ok I have heard many times that you should never save a calculated field in a table. I know this is breaking the rules but can someone tell me how to save a specific combo value change to a table? and avoid all the rest?
Here is my problem, I was asked to have a combo box that has 5 choices and when the default is changed to another choice, I must have the date saved for reference. But the date is only supposed to be saved when a specific option in the box is choosen. Such as
Default: basketball
was changed to football
I can do a timestamp but don't know how to change it when a specific option is selected. It just changes when it is updated , even when I change it to baseball
Im actually saving it but can't annoy the other options...
Can someone help me out here PLEASE
View 1 Replies
View Related
Apr 26, 2007
I have a report - I have a agent header and code header.
It should look like this:
Jeff Moenning Agent Header
1-Active Code Header
Report Details
For some reason the first page just has Jeff Moenning and nothing else - after that the pages are correct. They are breaking when the agent changes and also when the codes change within the agents.
View 3 Replies
View Related
Feb 5, 2008
Hi All,
I have a field with a bunch of records. All is formatted the following:
CAN-2007-US-00001
CAN-2007-US-00002
CAN-2007-US-00003
CAN-2008-US-00001
CAN-2008-US-00002
CAN-2008-US-00003
and so on and so forth. Is there a way Acess can break the fields out? I want 1 field with CAN, 1 field with the year, 1 field with US, and 1 field with the number (4 different fields broken by the hyphen).
I can do text to column in excel before I import, but just wondering if it can be done in Access. Thanks in advance, guys...
Caliboi
View 10 Replies
View Related
Dec 19, 2007
I am attempting to create a report that breaks down a field of 'ClaimID' numbers into groups of x. In the sample report below x = 12 and the report will apportion the first 12 'ClaimID's to the first page and textboxes with extra large fonts will signify the start and end of 'ClaimID' numbers for that page. These sheets are used for sorting and pulling guides at our local Xmas project and x will vary depending on the size of the facility we're using.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport.gif
I believe I can attain my goal if I were able to create a query which broke down the field 'ClaimID' into multiple fields based on x. The sample below represents this breakdown creating multiple records with x, 4 and 5, amount of fields.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport2.gif
Does anyone know how I can create this query, or perhaps suggest another way to create this report using the existing 'ClaimID' data.
Thanks in advance,
Aldo
View 2 Replies
View Related
Feb 17, 2006
Let's say that a table has been broken in two and I'd like to rejoin them with a query. Let's say 100 records in table1, 20 records in table 2. Here's a random example, I just made the names up off the top of my head:
PersonTable (100 records)
Person # (unique key)
Name
Address
SpouseTable (20 records)
Person # (lookup wizard to Table1, cascading edit/delete relationship, 1 to 1)
WeightOfSpouse
HeightOfSpouse
Many people aren't "married" and so have no corresponding value in the Spouse table. If I do an inner join on the two tables, on Person#, then it will cut the total # or returned records in the query to 20, to match the total in SpouseTable. Essentially, I want to append the results of SpouseTable to the end of the corresponding rows in PersonTable.
View 1 Replies
View Related
Nov 13, 2013
I have a report in which there is a subreport that prints towards the bottom of the page. I know that I can use grouping to have the subreport print on the next page if the entire subreport cannot print on the current page. However, I want to have the break occur not for the section as a whole but between records. Let me illustrate this.
All sorts of other stuff prints at the top. At the bottom, I have a subreport which today prints the following on the first page and breaks the last record in two printing the second line on the next page.
First Page of the Report:
__________________________________________________ _______________
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
__________________________________________________ _______________
Second Page of the Report:
__________________________________________________ _______________
Doctor's Location: XXXXXXXXXXXX
__________________________________________________ _______________
What I would like to do is the following:
First Page of the Report:
__________________________________________________ _______________
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
__________________________________________________ _______________
Second Page of the Report:
__________________________________________________ _______________
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
__________________________________________________ _______________
Is it possible to specify that if a complete record within a subreport cannot be printed on the same page to break to the next page without having the whole subreport break to the next page?
View 4 Replies
View Related
May 9, 2007
I have a date field on one of my forms that gets populated automatically when a new record is created. I also have a subform on the form that lists the tasks that still need to be accomplished this month.
Some problems have popped up as people got new computers with new versions of Access:
Access 2002(10.6501.6825) SP3 - No problems.
Access 2002(10.6771.6817) SP3 - The Date field isn't recognised on a new record (#Name?), but it's fine on the old records (displays date).
Access 2002(10.6771.6825) SP3 - The reminder subform is blank (white), as if it doesn't exist, but the date field works fine.
Any ideas why this might be/how to fix?
Thanks! This is really frustrating. If it didn't work for anyone, I would find a way to fix it, but because it works for some people, I don't know what to fix...
View 2 Replies
View Related
Nov 30, 2013
I have a situation where i need to import a table in Access which is in Excel, After importing i need to know how can i break it up differently keeping relation of FK and PK intact: E.g.
Excel Sheet:
Name Biz ID Biz Name Address1 Address 2 Address 3 OrderNo Order Date
Person Record
ID(PK) Name Biz ID Biz Name Address 1 ....
Order Record
OrdIDPK ID FK OrderNo Order Date
How can i distribute it?
View 1 Replies
View Related
Dec 20, 2013
I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?
Raw Data
EmployeeActivityTimeStampStartTimeStampEndTotalDuration
Doe,JohnSomeActivity12/16/13 9:06:02 AM12/16/13 9:57:14 AM0:51:12
Smith,JaneOtherActivity12/16/13 9:22:15 AM12/16/13 10:06:55 AM0:44:40
Query to break out the total duration time in to the 15 minute interval it fell into
EmployeeActivityIntervalIntervalDuration
Doe,JohnSomeActivity12/16/13 9:00:00 AM0:08:58
Doe,JohnSomeActivity12/16/13 9:15:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:30:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:45:00 AM0:12:14
Smith,JaneOtherActivity12/16/13 9:15:00 AM0:07:45
Smith,JaneOtherActivity12/16/13 9:30:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 9:45:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 10:00:00 AM0:06:55
View 2 Replies
View Related
Nov 12, 2013
I have a report which includes several subreports. There will be times when a subreport prints partially on one page and completes on the next. I don't always want to break to a new page with this subreport. However, I would want to break to a new page if the subreport cannot fully print on the current page.
View 3 Replies
View Related
May 16, 2007
Can anyone give me any guidance on how to use IFF statements in microsoft access? or Has anyone got a link to a good website that can help me? I need to use the formula below to determine if the quantity of an order from a store is greater than the quantity that is in stock at the warehouse and produce a message message.IIF([quantityordered])(=[quantityinstock]"enoughstock","notenoughstock") I just dont know where to start, i didnt even know you could enter formulas in Microsoft Access so i really need help
View 2 Replies
View Related
Mar 8, 2007
Hi, I have another problem with my Access database now...
I am trying to disable fields when a certain value is chosen. I can do it for one field with this code:
If ALLOCATION_LP = "Other" Then
OTHER_ALLOCATION_LP.Enabled = True
Else
OTHER_ALLOCATION_LP.Enabled = False
End If
That one works perfectly well, but I have another one that needs to disable two fields instead. If they choose "No Extant PP", I want it to disable two fields; one combo box - PLNG_PERMISSION_STATUS_2 (which I can get to and it works) and also an "Other" text box - OTHER_PLNG_PERMISSION_STATUS_2 (this is where i get stuck). I have tried two If statements, but whether I'm writing them wrong, I'm not sure.
I would really appreciate the help. Thanks.
View 2 Replies
View Related
Feb 5, 2008
'construct a where clause as required
If Nz(Me.Manufacturer.Text, "") <> "" Then
Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
End If
Me.list.RowSource = _
"SELECT ID, Description, Par, MaxCoins, PayLines " & _
"FROM MachineTypeQuery " & _
Where1, Where2 & _ <- how do i get something like this to work
"ORDER BY Description;"
Exit Sub
Where2 comes from another control and has to be filled in before they get to this control. also "where1" and "where2" are set as public stings
View 6 Replies
View Related
Feb 25, 2005
I want to check eight Yes/No fields. If none of the boxes are checked I want to display a msgbox. I can link seven of the fields (no problem), when I add the eighth it doesn't work.
Am I limited to seven fields in my statement? Is there a way around this?
Any help would be appreciated.
============
If Me.Printer = 0 And Me.Hardware = 0 And Me.Software = 0 And Me.MSOffice = 0 And Me.Network = 0 And Me.NewComputer = 0 And Me.Profile = 0 And Me.Other = 0 Then
MsgBox "You must enter the type of problem(s) addressed with this customer", vbQuestion, "Type of Problem(s)"
Else
blah blah
End if
View 2 Replies
View Related
Nov 5, 2004
I want to select all records where a specifc field data start with specific data.
I'm having a meltdown because I know if is easy. If have tried Like "*" but that doesn't work. What is the command for Starts with?
View 4 Replies
View Related
Feb 28, 2007
My database has a field named "Note". This field will either have a "Y" or "N" in it. At the bottom of a report I'm writing, I want to count the number of "Y's" (not the "N's") at the bottom of my report. Can someone tell me how to do this? Thanks so much in advance!
View 1 Replies
View Related
Apr 2, 2007
hey all
i have a list of values that i'm trying to run through an iif, like so:
IIf(([Warehouse Location].[ToAISLE] Like "1" Or "2" Or "3" Or "4" Or "5" Or "6" Or "7" Or "8" Or "9" Or "10"),([Product Inventory with totals].[Available]+[Warehouse Location].[Qty]))
im doing that in a query, not the vb code. now heres my problem: that's not the correct syntax for the Like blah blah blah part. does anybody know the correct syntax for this?
thanks!
View 2 Replies
View Related
Jun 5, 2007
Within my database I have 5 fields that will have either "Yes" or "No" data. I want to be able to have one of these fields look at the 4 others and if they meet a condition have the background of the field change colors.Here's a breakdown of what I'm trying to do:
Field1 Field2 Field3 Field4 Field5
Yes Yes Yes Yes No
No No No No Yes
If Field5 has a "Yes" and Fields 1,2,3,4 are all "No" then I want Field 5 background to change color to indicate that a criteria was met.
Is there a way to do this? Thanks for any help.
View 1 Replies
View Related
Mar 7, 2007
Hi.
I need to export a set of sql statements from my Microsoft Access database in order to put it into my new mySql server. I know that its possible to get the statements off the mysql database, but i cant figure out how to do it in microsoft access. Any help is appreciated
Thanks in advanced,
Teh mockers
View 1 Replies
View Related
Mar 19, 2007
Hi,
I hope I posted this in the right forum. It didn't seem specific enough for the other ones.
I have a select statement in a form that reads as follows:
Select [Port of Origin] from [Port of Origin]
It works great. It does exactly what I want it to do.
I want to add another field to the statement but it is from a differnet table. If it was to stand alone it would read as follows:
Select [VendorName] from Container_Vendor_Information
How would I combine these two statements? I have tried various things can't get it to work. Any thoughts?
Thanks so much in advance.
Eddie.
View 2 Replies
View Related
Jun 1, 2005
Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.
I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.
SQL Statements
FIRST QUERY
SELECT tblApp.Time
FROM tblApp
WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));
SECOND QUERY
SELECT tblTime.Time
FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time
WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));
Rixx
View 4 Replies
View Related
Sep 7, 2005
Is it possible to have operators (<, >, <=, >=) in an IIF statement like this:
iif([field a] is null, > [field b], < [field c])
This is in the criteria for a currency field.
I have been trying different combinations without any luck for the last couple of hours or so. :confused: :confused:
Any help would be greatly appreciated.
Thanks in advance,
Brian
View 4 Replies
View Related
Sep 14, 2005
I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.
SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number,
[AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry,
dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number,
Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code,
vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2
FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number)
LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number)
LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory
WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))
GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType,
dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number,
vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0
HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel]))
AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number]))
AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType]))
AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));
View 2 Replies
View Related
