I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
IIf(Time()>Hour(11),Date(),DateAdd("d",1,Date()))
I have a complex IF Statement within VBA. When I step thru the code the if statement variables should return true but instead treats it as False. See '>>>> this happens on the 2nd Pass of a Do Loop Statement, the First Loop the if Statement Returns True
Variable Values:
Record 1
contractNo: 00001634
nfld: 33.40%
nTier6: 30.00%
Record 2
contractNo: 00001634
nfld: 137.52%
nTier6: 28.50%
so the 2nd if should be true but it acts as false an moves to next If statement.
Code:
If nfld = Format(0, "Percent") Then
nOvrAmt = 0
BkOvrCalc = nOvrAmt
GoTo cont:
'>>> ElseIf nfld > nTier6 Then
nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields("T6E").Value
[Code] .....
I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code:
Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
Code:
Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))
I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
Hi all,
My problem is as follows:
I am trying to generate a report, which is based on a query, which references a form that has an option group containing four options.
I want to sort the data according which option group radio button has been selected (they the values of 1,2,3,4).
Originally there were only 2 options and the query said (in SQL view):
ORDER BY IIf(Forms!frm_08_reportsort!opt_sort_by=1,TBL_Acco untManager.Name,TBL_SiteContactDetails.SchoolName) ,
This works fine for 2 options, but I can't figure out what command i should use for more than 2 options :confused:
Any help would be much appreciated :)
I have two text boxes for which I am trying have show and error message when the proper data is not entered. One text box is for PLANID and the other is for Date. I would like the PLANID to be checked 1st. The PLANID needs to be either 7 digits or 10. If it is not, the message would be "Please enter a valid PLANID". If it is 7 or 10 digits, then it should check the Date text box.
If it is blank, the message would be "Please enter a valid Date". If they are both blank, the PLANID message should appear 1st. Below is what I have written. The problem that I am having is that when the PLANID is 10 digits and the date is blank, the "Please enter a valid PLANID" message is showing rather than the "Please enter a valid Date" message.
Private Sub Btn_Refresh_Data_for_One_Plan_Click()
Me.txtboxPLANID.Value = UCase(Me.txtboxPLANID.Value)
If ((Len(Me.txtboxPLANID.Value) = 7 And Len(Me.txtboxPLANID.Value) = 10) And (Not IsNull(Me.txtboxPLANID.Value)) And (Not IsNull(Me.TxtboxDate.Value))) Then
Dim stDocName As String
stDocName = "Mcr_RUN_MATCH_DIFFERENCES"
[code]....
I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.
Here's the logic:
True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"
False: "GS"
This should be really simple, but I am stumbling on the criteria. I have a combo box which returns "Y" or "N". I'm using this value as criteria for a field called [Planned=Y/N]. This field stores either a "Y" or a "N" to indicate that a network outage was planned or not. If the combo box is "Y" I want to return all values(Y and N). If the combo box is "N" I only want to return values of "N". I've tried the following in the criteria without the desired affect:
IIf([Forms]![frmOutagemenu]![cboflag]="Y","","N") - only returns records when "N" is selected.
IIf([Forms]![frmOutagemenu]![cboflag]="Y",null,"N") - only returns records when "N" is selected.
IIf([Forms]![frmOutagemenu]![cboflag]="Y",In("Y","N"),"N") - too complicated for Access to decode.
Is there another way to return nothing for the criteria when "Y" is selected?
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
Hi Everyone,
I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks.
AM=700-1399 PM=1400-2299 NIGHT=2300-699
Hi all,
May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time.
Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping.
Thanks !!
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies View RelatedTrying to import some data from a linked Excel spreadsheet into a local table. One of the fields is a Date/Time type and is recorded in EST (Eastern Standard Time). I want to keep this field for posterity but also add a separate field with the corresponding time as per BST
For clarity, daylight savings time comes into effect this year on 26th Oct in the UK and 2nd Nov in the US. So generally, there is a 5 hour difference between the two time zones, apart from the period between these two dates, when it is only 4 hours.Here is my query - I am using a SWITCH function to create the BST field
Code:
INSERT INTO tblTransactions
SELECT ltbPayments.ID AS Reference, ltbPayments.VALUEDATE AS ValueDate, ltbPayments.LOCALAMOUNT AS Amount, ltbPayments.USDAMOUNT AS AmountUSD, tblAccounts.AccountID AS AccountID, ltbPayments.TRANSACTIONTIME AS TransactionTimeEST,
SWITCH(DateValue(ltbPayments.TRANSACTIONTIME) < DateSerial(2014,10,26) Or DateValue(ltbPayments.TRANSACTIONTIME) >= DateSerial(2014,11,2),
[code]....
So - how do I explicitly specify the output of the SWITCH function to be in Date/Time format (I presume, by default, it's returning Text, which contradicts the table properties of tblTransactions & the TransactionTimeBST field?...)
Dear All:
Thank you for all you help with the above title. Your suggestions were all implemented.
Code:
Private Sub GPA_AfterUpdate()
If DIVISION = GRADUATE Then 'Honors field is empty
HONORS = ""
End If
If UNDERGRADUATE = True Then 'Apply appropriate honors
End If
If GPA < 3.2 Then
HONORS = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
HONORS = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
HONORS = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
HONORS = "SUMMA CUM LAUDE"
End If
End Sub
Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.
When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.
Please help,
Dion
I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)
So if I type <start date> 01/01 and <end date> 02/01 the query will return:
01/01/04
01/01/05
01/01/06
02/01/04
02/01/05
02/01/06
Does anyone know a solution - I have been searching all afternoon!!??
Hi All
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1)
I have the following in a field called PROBLEM STORE NAME:
SALE (DAY 1 - HD)
Mt DRUITT (WOWPOS) (E)
THE MALL (WOWPOS)
TOWN HALL (RF)
LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:
=IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2)
I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:
=IF(ISBLANK(X2),"NO","VENDOR CALL")
Any suggestions would be greatly appreciated
cheers
sallee
Dear Access Wizards:
I have created a form based on a query with students' gpa in the form. The gpa is in a specific field.
There is another field for honors with is for summa cum laude, magna cum laude, cum laude.
Here is the issue: If the students' gpa is 3.8, return "summa cum laude"
If the students' gpa is 3.5, return "magna cum laude"
if the students' gpa is 3.2, return "cum laude"
This applies to undergraduate students only, which there is a field for.
Any ideas on how to resolve this issue?
Many thanks,
Dion
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
View 1 Replies View RelatedI have a database (small one which i designed)
I have created all the tables.
I then have a "form" called July-14 (i need to figure out how i can automatically replicate this form for new months)
Anyway, one of the cells pulls out a product name (using the lookup wizard), i then have a cost cell which i would like to populate automatically based on the product name.
The table July-14 links to a product table which has ONLY those 2 attributes in there, the name and cost.
How do i get it to pull the cost automatically.
I have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?
I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.
I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).
I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?
Any help would be appreciated.
I have the following tables: Rates and SelectedDates, which are structured as follows.
Rates: RateID, EffectiveDate, Rate
SelectedDates: Date. Table is bound to a combobox in which user selects one or more dates, mm/dd/yyyy, which is the same format as Rates.EffectiveDate.
I need a query to return the first Rates.Rate for the following conditions. (there will be a minimum of one record in the Rates table)
IF there is only one Rates.EffectiveDate for the SelectedDates.Date
return the associated Rates.Rate
ELSE IF count(Rates.EffectiveDate <= SelectedDates.Date)>=1
return the first Rates.Rate where Rates.EffectiveDate <= SelectedDates.Date
ELSE
return Rates.Rate associated to the Rates.EffectiveDate closest to the SelectedDates.Date.
I hope this is clear, as it is late and I am tired...:confused:
Thanks in advance,
Rod
Dear All:
I have a form with three items:a checkbox called "Check231", a textbox called "text921" and another textbox called "text762".
What I wish to do is: Enter text in textbox921, which stays the same as I scroll through each record. Then If checkbox check231 is checked, it displays text from textbox921 to textbox762. Textbox762 is bounded to the form.
Any ideas on how to get started?
Many thanks,
Dion
I inherited a spreadsheet that uses an Index (Match formulate to return a value. I want to use Access to manage the data going forward. I am just starting to build my tables in Access so I have some flexibility and want to do it right. Here is a sample of the data:
KeyEnroll JanFebMarAprMay
abc1/2013103521
def3/201300789
ghi5/201300008
So if Enroll date starts in Jan I want the 10 amt returned from the Jan column. If the Enroll date starts in March, for that client I want the March amy returned, etc.
results: for client "DEF" the amt returned should be "7" because their enrollment date is 3/2013.
Event#
EID
Arrived Time
Code
Cancelled Time
[code]...
I need to find all EIDs where the Code is 611 where an Event# starting with F was Cancelled along with the time of the cancellation. For these results I also need the Arrived Time for the Event# starting with E for that EID.For instance, a result I'm looking for would be:
EID
Event# (F)
Cancelled Time
Event# (E)
Arrived Time
[code]...
Good day,
I was wondering if I can get some help with my sql statement, b/c I am a run-time error. I have attached my db but the statement is as follows:
SqlStr = "INSERT INTO tblpcomaward (recempid, reclname, recfname, recdate, givempid, givelname, givefname, desc, value, rebill, comments) Values" _
& "('" & Forms!frmpcom!txtempid & "', '" & Forms!frmpcom!txtlastname & "','" & Forms!frmpcom!txtfirstname & "', #" & Forms!frmpcom!txtdategiven & "#," _
& "'" & Forms!frmpcom!txtgiverempid & "', '" & Forms!frmpcom!txtgivelname & "', '" & Forms!frmpcom!txtgivefname & "', '" & Forms!frmpcom!cbodesc & "', '" & Forms!frmpcom!txtvalue & "', '" & Forms!frmpcom!cborebill & "', '" & Forms!frmpcom!txtcomments & "')"
Thanks for any help.