If the following works for a single criteria in a where statement.
Code:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblProcedures WHERE ProcedureID = " & lngMoveUpID, dbOpenDynaset)
What would a second criteria look like.
I'm trying to create a report that does the following:
If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.
If Not IsNull(Me.Time1) Then
If Me.Time1 = "Other" Then
Me.Time1.Visible = False
Else
Me.Time1.Visible = True
[Code] ......
I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:
Code:
strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3
You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.
Is there a simple way to build this string based on the number of search strings that are not null?
I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.
[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.
Dim maxFlag As String
Dim flagCriteriaWarranty As String
Dim flagCriteriaEstimate As String
[Code] .....
I'm trying to get a total based a combination of criteria. Here's my issue:
If ([Vendor Billed Amount] > 650 $ and $ [Excess Fee Approved] = "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
If ([Vendor Billed Amount] < 650 $ and $ [Excess Fee Approved] <> "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
endif
endif
I think I'm missing an argument with the nested If statement.
Any Single line of code available?
View 2 Replies View RelatedI am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
SQL stored procedure which is simple INSERT statement on a single table 'tblSOF'
Code:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter values below.
[Code] ....
I am stumped with the following error.
Error: Msg 102, Level 15, State 1, Procedure InsertINTO_tblSOF_sp, Line 80 Incorrect syntax near ')'.
I have a query that displays all records. I need it to limit based on multiple criteria in a single field. I.E... instead of all employees from every section, I just want it to display employees from section A, C, D, F one time and next time maybe go with section B, C, D. For some reason the solution eludes my little brain.
View 3 Replies View RelatedHi,
I wanted to know if it is possible to combine multiple criteria in a single dialog box. Let me explain:
I have a query for which two fields need criteria entered by the user. In design view under criteria I have entered this type of expession Code:[enter criteria] for both my fields. Of course when I run the query I first get prompted for the first criteria and then once I click OK I get prompted for the second...
What I would like is to have a single dialog box with both criteria on 2 lines...
Your help would be mcuh appreciated
I have a database structure with a series of queries which are all inter-linked.
I want my database to be user friendly, what is the cleanest way to be able to enter a single variable eg as a criteria for a query - say the date, rather than having to change it in a series of queries manually, can I call it from somewhere?
I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.
My table is in the form,
TimeandDate,WS127m_Avg,WS82m_Avg....
I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,
SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s
FROM CL_AllData
WHERE (((WS127m_Avg)=9999))
GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);
I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).
I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.
I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.
Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.
The code I'm using in each criteria box is;
Like "*" & [Forms]![MyForm]![Combo1] & "*"
and this works as a single criteria in a single field.
I have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
Hi Gent’s
This is my first posting here,
I’m looking to put a criteria on an IIF statement,
Hol_Period: IIf([CAN_Date]="False" Or [CAN_XM]="False" Or [CAN_paper]="False","True","False")
This will output either True or False, True if any of the other 3 fields contain a False and False if all contain a True.
How can I put a criteria on this to show me only True’s, without using another query???
Thank you
Jason
A database I'm creating contains records of events within an academic year. There will be many events in the table, and I want to create a query to show me only those events in the current academic year.
I figure that I need to use some sort of iff statement to make this happen; something which says:
IF the current date is before 31-Aug-CurrentYear
THEN display records in the range 01-Sep-LastYear and 31-Aug-ThisYear
ELSE display records in the range 01-Sep-ThisYear and 31-Aug-NextYear.
I've had a go at writing this, but can't get my code to work. Can anyone help?
Thanks in advance,
Gary
Is there a problem with using an IIf statement in the criteria of a query. If ther isn't then is there a problem with using a Between...And statement inside the IIf statement. Or, does anybody see a problem with this IIf statement being in the criteria of a query.
IIf([blinks_test_end].[Blinks]<>[blinks_test_start].[Blinks],([daily_extract].[Reading_Date]) Between [blinks_test_start]![Reading_Date] And [blinks_test_end]![Reading_date],Null)
[blinks_test_end] and [blinks_test_start] are queries that use the table [daily_extract] the query that I am trying to run uses these queries and the table, however, I have nothing joined. The IIf statement is in the criteria of the [daily_extract].[Reading_Date] field.
I am working on one access database wherein in query i need to use iif statement, i have one field called Age, in field criteria i need to use,
IIF(weekday(DATE())="2",>"3",>"1")
this if staement is not working i believe that we cant use ">" ... this type of criteria with iif, so what should i do?
my intention is if today is Monday than age filed >3 else it should be >1
Here are the criteria I am using:
Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())
Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())
Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1
I am able to successfully set one of the above as a criteria just fine.I am stalled on trying to combine two or more of the above in an iif statement. Here is what I 'think it should look like:
Funded_Date_Period: IIF([funded_date] = "Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1)", "PrevMonth", IIF([funded_Date]= "Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())", "CurrentMonth", IIF([funded_date]="DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())", "CurrentWeek")
In other words, in a separate field, I would like to print a period name such as above.I have not been able to find any reference to connect these long criteria together in one query expression.
My workaround is to use a union query. That works, but it is a little clumsy due to some outer joins which require a separate query. If that is my only solution, I will run with it.
I have the following statement entered in the query criteria section.
SELECT MAX(EDRMPeriod) FROM EDRAppC WHERE EDRMPeriod Between DateAdd("yyyy",-1,DateSerial(Year(EDRMPeriod),Month(EDRMPeriod)+1, 1)) AND DateSerial(Year(EDRMPeriod),Month(EDRMPeriod)+1,1) GROUP BY EDRCHPlan
I am getting a syntax error. This statement runs fine and produces the results I want in Queryman.
Thanks for any help.
k
I have the following IIF statement in the criteria of a query (design view). There are three options: (1) Confirmed (2) Failed (3) Pending. Options 1 and 2 are working fine however when option 3 is selected, no records are returned... not quite sure if I've written the IIF correctly for option 3.
IIf([Forms]![Pending_Bookings]![Confirmed]=-1,"Booking Confirmed") Or IIf([Forms]![Pending_Bookings]![Failed]=-1,"Failed Booking") Or IIf([Forms]![Pending_Bookings]![Pending]=-1,([Status_Codes].[StatusName])<>"Booking Confirmed"<>"Failed Booking")
any help appreciated..tnx :)
I need to use square brackets as part of the criteria in a LIKE SQL statement. The criteria is:
[ABC123][SR]
ie, find all records which have that as part of a longer text string. How can I amend the SQL statement below to "escape" the square brackets and treat them as part of the string?
SELECT * FROM p_Overall WHERE (((p_Overall.Assigned_Dept)="ICONS_IMAC")) OR (((p_Overall.Brief_Description) Like '*[ABC123][SR]*'));
Help!!!!
Thanks in advance :-)
I'm working on a published sharepoint web access database, writing a criteria expression in data source of a combo box.The field I'm setting criteria is called SharePointAuthor.
I wrote this critea:
IIf(CurrentWebUser(1)="John Doe","*",CurrentWebUser(1))
Trying to show all records when currentwebuser is John Doe, otherwise, show only records created by currentwebuser.Funny thing is that it turns out "John Doe" couldn't see any records, while other users can see their own records as expected.
I'm trying to create a button that posts an order and before it post certain criterias need to be meet. One of them is a date needs to be selected in the order, if not a popup show to explain. The trouble I'm having is referring to todays date in the VBA code. I've been reading and trying different things but not getting anywhere, hopefully someone can fill me in.
This is the code I'm working with.
If [PayOrderMoneyReceivedSubform].[Form]![OrderDate] = TodaysDate Then
dosomething
else
donothing
end if
The part I'm having trouble with is TodaysDate, what should I be putting there?
Hello everyone! :)
I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!
Ok I have this table called CustomerComments. In it are the following columns
CustID
Comment
CommentDate
For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)
Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.
Does anyone know how I could just get that top line per custID rather than all rows returned?
Thanks for reading this... hope it makes sense!
:)
In my query, I am looking to set the criteria by having the user pick 1 of 3 options, or a combination of those three options. Here's an example:
The fields are Manager, Employee, Job Function. Let's also say that my criteria is David, John, Busy Work. I want to be able to pull results by David, John, Busy Work, or a combination of those three.
My Query for picking 1 of the 3 looks like this:
Field: Manager Employee Job Function
Criteria: David
Or: John
Busy Work
When I try to add additional Or statements, it never works. An example would be in the fourth line of the criteria, I add:
David John
Running the query produces only the results for David. Mixing it up produces only the results from the first item in the criteria (Manager over Employee over Job Function).