Queries :: Incorporate A Date In Output To Statement

Jun 11, 2013

I have a database that enables the banking details for a small club to be generated as an Excel spreadsheet that is then emailed as an attachment to the treasurer. The event procedure below works fine but I have been unable to incorporate the banking date from a control called BankDate to end up with the Excel file being in the form DGC Banking dd-mm-yy - is this possible?

DoCmd.OutputTo acOutputQuery, "Export Banking", "Excel97-Excel2003Workbook(*.xls)", "DGC Banking.xls", True, "", 0, acExportQualityPrint

View Replies


ADVERTISEMENT

Using Switch Statement In Access Query - Output Integer?

Mar 27, 2013

I am trying to use a switch statement in a access query and i be leave i have the code right but for some reason it will only output a string i need it to output an long integer . (All the columns that are in the statement are Numbers).

Code:
columnName:Switch (([column1]<>0), [column1], ([column2]<>0), [column2], ([column3]<>0), [column3],([column4]<>0),[column4],([column4]=0),0)

Outputs a string i need an long integer

I have tried

Code:
columnName:Switch (([column1]<>0), CLng(column1), ([column2]<>0), CLng(column2), ([column3]<>0), CLng(column3),([column4]<>0),CLng(column4),([column4]=0),0)

and does not work just outputs a string. It outputs the right number but not as an integer...

View 1 Replies View Related

Queries :: IIF Statement With Date Functions

Feb 24, 2014

My goal is if the current date = 1st - 4th of the month to return the failure_date if it is >= 1st of the prior month and <= 4th of the current month (ie, 01/01/14 - 02/04/14)

this works:
>=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4)

But if the current date > 5th of the month to return the failure_date if it is >= 1st of the current month and <= 4th of the next month.(ie 02/01/14 - 03/04/14)

this works
>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4)

But when I put it in the iif statement if will not work:

IIf(Date()>DateSerial(Year(Date()),Month(Date()),4 ),>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4),>=Dat eSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4))

SQL =
SELECT table_testing_dates.Failure_Date, table_testing_dates.Failure_Date, table_testing_dates.FailureGrouping
FROM table_testing_dates
WHERE (((table_testing_dates.Failure_Date)=IIf(Date()>Da teSerial(Year(Date()),Month(Date()),4),(table_test ing_dates.Failure_Date)>=DateSerial(Year(Date()),M onth(Date()),1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date())+1,4),(table_testing_dates. Failure_Date)>=DateSerial(Year(Date()),Month(Date( ))-1,1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date()),4))));

View 2 Replies View Related

Queries :: Nested IIF Statement In Date Calculation

Jul 30, 2015

I am trying to calculate the time between two dates where one date field might be blank or not. Where the field is blank I want to use the current date to perform the calculation. So far I have the following but I keep receiving an error message saying that the expression has a function with too many arguments. Is there a simpler solution to this?

IIF([LastOfEnd_Date] IS NULL,(DateDiff(w,[LastOfDischarge_Date],NOW()))/4, IIF(Not isnull([LastOfEnd_Date], Abs(DateDiff("w",[LastOfDischarge_Date].[LastOfEnd_Date]))/4

View 5 Replies View Related

Queries :: If Statement - Find Cumulative Forecast Up Until A Best Before Date

May 22, 2013

I want my query to find the cumulative forecast up until a best before date.

This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.

I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.

How would I go about this?

View 4 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Output Format Of Date/time

Sep 3, 2005

Hey guys, been away from Access and SQL for some time. I have a simple 2 column table with one thats set to Date/Time that displays the date only. Unfortunaly, when I run my query, the date comes out 7/9/2005 and I want it to come out as 7/09/2005. I know I am going to have to force it out with a format command, but its been so long that I have no idea how to. Right now my query, in SQL format, looks like this.

SELECT * FROM Table1 ORDER BY Date DESC

I just need the format command to force the date. If anyone can help, thx.

View 1 Replies View Related

How To Incorporate External VB Program Into Existing DB

Feb 8, 2012

I have a few homemade VB programs that I use for my job that I want to integrate into my groups contact db. What is the best way to do that?

View 1 Replies View Related

Way To Incorporate E-Signature Box In Access Form?

Aug 11, 2014

Any way to incorporate an E-Signature box in an access form?

View 3 Replies View Related

Tables :: How To Incorporate Selling A Product Option

Dec 11, 2014

You have a company that sells a small line of very exotic, non-perishable, chemicals to manufacturers.

You source these chemicals from around the world and resell them to the manufacturers.

The chemicals are not only very expensive, but may or may not be required for any given batch of product the manufacturer is producing.

The manufacturer does not know if the chemicals will be required, or not, until the last minute, so must have them immediately available for each batch.

You keep track of the process with a simple database that is built around a "Chemicals" database and a "Transactions" database. Which are linked by [Product Code]

Quite straight forward to this point.

-----------------------------------------------------------------------------------------------

Now

You are seeking a way to make these chemicals accessible to manufacturers who cannot afford to buy them, just to have them sit around waiting for the day they are needed.

You set up a system where a manufacturer can purchase an "Option" on the chemicals whereby:

They pay you a certain amount of money for the right to hold the chemicals at their plant for a given period, denoted in weeks.

After the pre-determined period is over they must either:

Return the chemicals to you and you keep the money they paid for the option.

If they used the chemicals, pay you the pre-determined purchase price --you still keep what they paid for the option.

----------------------------------------------------------------------------------------------

My problem is incorporating the options into the database built around "Chemicals" and "Transactions," e,g,:


(1) The "Chemicals" table has fields such as [Product Code],[Quantity in Stock],[Cost],[Sales Price per Kilo]. The options require a unique set of fields such as [Product Being Optioned], [Expiry Date of Option],[Sales Price per Kilo -If Used],[Weekly Price for Option]

It doesn't seem correct to jam these into the same "Chemicals" table although I could be wrong.

(2) The same is true for the "Transactions" table, i.e., it now identifies the chemicals in each transaction using [Product Code].

To identify an option in a transaction one needs to use [Product Code] and [Expiration Date of Option]

(3) When a chemical is optioned the company has to track the fact that that quantity of it is not available for outright sale until the option expires

(4) A further problem exists even if I have figured out how to structure the tables.

Before selling a chemical one must create a record for that chemical in the "Chemicals" table--not a problem.

Options on the chemicals, however, do not exist until they are sold i.e., instead of using the "Chemicals" table to look up data for each transaction. Each transaction (to sell an option) has to create a record in the table being used to track the options

View 5 Replies View Related

Forms :: How To Incorporate Pivot Tables In Access Form

Mar 25, 2014

How to incorporate access pivot tables in an access form? Also how to subtotal on changes withinin a field in a form?

View 4 Replies View Related

General :: How To Incorporate Schemes Of Work - Adding Elements To Database

Jun 25, 2014

I am currently exploring the possibility of adding another element to our Database.

Each of our courses has a scheme of work associated with it, and this is completed on a daily basis by the tutor for each student. These are currently just Word documents we print out.

Example : I already have provided provision for recording attendance in the database, see below, (it may look a bit strange, but our training is ad-hoc so slightly different requirements than a college for example).

View 10 Replies View Related

Queries :: Adding Value To Query Output

Dec 12, 2013

I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times additional hours may be requested by the customer.

I want to add a another field to this query that will prompt me if their were any additional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the value entered plus the standard coverage hours in the other field.

I'm not sure how to set up the new field.

View 10 Replies View Related

Queries :: Change Output From Check Box To Y / N

Jan 30, 2015

I have created a query and one of the columns of the output displays a check box because this is the data type of the underlying table.

I would like the query to output either Y or N rather than displaying the checkbox.

How would I do this?

View 3 Replies View Related

Queries :: Modifying Query Output

Feb 13, 2014

I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".

View 5 Replies View Related

Queries :: Expression To Output Value As A Number?

Apr 17, 2014

I've created several expressions in a query to test for values in a field (one column for each value for use in a report).

The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.

I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this.

View 5 Replies View Related

Queries :: Sum 2 Expressions - Output With 3rd Expression

Apr 26, 2013

I'm trying to sum up 2 expressions.

1st. UsedAF: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID]);0)
2nd. UsedRES: nz(DSum("RES_lengte+RES_zaagbreedte";"VRRESSTAF";" VRID = " & [VR].[VRID]);0)

Now with a third expression I like to sum those 2 up.

3rd. Used: [UsedRES]+[UsedAF]

The thing I run into is when 1st has a value of 15 and 2nd a value of 0 the outcome of 3rd is 150 while it should be 15 ofcourse.

When I change the first 2 expressions to Sum (at Totals) the 3rd works properly. Yet the other 2 comes with wrong values.

View 5 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related

Multiple Queries Output To Be Displayed In The Form

Dec 15, 2004

Hi,

I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments
???

View 2 Replies View Related

Queries :: Multiple Records Appear In Output Of Query

Mar 4, 2015

i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.

View 2 Replies View Related

Queries :: Get Output Of Query In A Parameter (VBA Variable)

Jul 1, 2015

How could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:

Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)

Output of a query is a single MAX value.

View 4 Replies View Related

Queries :: Wildcard Like Not Returning Desired Output

Jul 8, 2014

I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".

View 12 Replies View Related

Queries :: Function To Output Line Numbers In A Query - YTD

Apr 18, 2014

Is there a built in function which can be used to create line numbers in a query?

I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?

View 1 Replies View Related

Queries :: Multiple Nested IIF Statements - Output In One Field

Mar 25, 2013

I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)

I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.

Can I change this to only output in one column or add some more sql to do so?

Here is the SQL (table and filed names have been changed to protect the innocent)

BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).

************************************************** *************
SELECT TABLE1.ID,
IIf([TABLE1]![STAGE]="D",
IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1",
IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",

[Code] .....

View 1 Replies View Related

Reports :: Output To PDF Different Than Printed Output

Aug 20, 2013

I'm using Access 2007 SP3.

Whenever I export reports to PDF, the output appears zoomed and clipped. No extra pages are generated as they would be if I'm going over margins, it's just the report is clipped.

The report looks perfect in preview mode, and it looks perfect when going to an actual printer. However, when using OutputTo to save it as a PDF, this is when the report content is clipped.

Here is the code I'm using:

DoCmd.OpenReport MyReport, acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "MyReport.pdf"
DoCmd.Close acReport, MyReport

I open the report in preview mode first so events are fired that show/hide various objects based on fields in the recordset.

I've tried reinstalling, and I've tried this on two different machines, one running Windows 7 and one running Windows Server 2008...both with the same results.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved