Qry Nested If's And Date Formats

Jul 11, 2007

I'm tring to build a queary based on a field called "PLAN_CODE" and a policy issue date field. There are 3 plan codes ...select1, select 2 and select 3.
I want to pull information as follows
Select1 has an issue date 11 months ago (day the queary is ran)
select2 has an issue date 23 months ago
select3 has an issue date 35 months ago

I built a field to calculate the duration of the policy based on the issue date
Duration: (Round((POLICY_ISSUE_DATE]-Date())/365,1)). I think the format is wrong? need help with that!

I built a field to create a nested "IF" statement but the first "IF" is not pulling correctly, so I cant built the remaining "IF" statements.
Expr2: IIf([PLAN_CODE]="SELECT01N" [Duration]=-0.9,0).

Anyone have a better approach? Any input how to handle this? I'm not real good with VB code but have a general understanding of it. It's easier for me to build in the QBE.

View Replies


ADVERTISEMENT

Date Formats In Query

Mar 20, 2007

Hi,
and again I have a problem :)

so,
In Form I have two calendar controls, and two text boxses. Based on selected date, text boxes are showing day of the year (format(Date(),"y")).

Query is based on table with records of days in year (numbers),
so, when Im trying to sort Between two numbers from Form (in this case nubers of days in year) query is reciving (imo) full date, not the number and im not getting any results.

Any non VBA ideas?

tnx

View 4 Replies View Related

Problems With Date Formats

Feb 23, 2005

Hi Folks,

I have a table of dates and a "frm Dates" based upon it. I have copied a very useful calendar control which I found on a sample datebase elsewhere on this excellent site. I want to have an [OK] button which jumps to the correct date in my frm Dates when I choose that date on my calendar, but it's getting the months and days the wrong way round. Here in the UK our favoured approach is dd/mm/yy, but to try and figure this thing out I've formatted everything as Long Date.

Further details are on the form of the attached database, I hope you can help. Non-technical answers most appreciated as I'm pretty much a greenhorn with this stuff.

Thanks all!

View 2 Replies View Related

Queries :: Calculating Different Date Formats

Jul 18, 2014

I have a query based on two tables that calculates days elapsed between two dates. Problem is one date field is date/time and the other is short date, so the result is always infinite decimal places. I don't want to change the date format for the date/time on the one table. I tried the format function in the query, but still ended up with decimal places.I just want to make that one long date to act like a short date for calculating "days", not hours, etc.

View 1 Replies View Related

General :: VBA Date Conversion To SAP Formats?

Oct 13, 2013

I've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .

a simple string conversion like

Code:

pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")

is not working, the year is converted wrong.

thus 17-07-62 should be converted to 17.7.1962 ( European date format )

View 1 Replies View Related

Forms :: Data Entry Textbox - Date Formats

Jan 26, 2015

I have a date entry textbox.

The user is copying a date from an old form.

Sometimes the form will have the date entered as 00/00/97

How can I change the month and day to 01/01 and of course leave the year as it was.

I've tried using split with no success.

View 12 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

Access2000 Nested IIf() Functions In Date/time Format

Aug 8, 2005

I am working on a report in Access2000 which is getting information from several tables via a number of queries.
I am using nested IIf() functions to combine text formatted data in a calculated query field before using it in a report.

One such IIf() function is

Desc_col: IIf([HOVR - UDP].[SERVICE] Is Not Null,[DA] & " " & [SEQ] & " " & [TYPE] & [MO] & " " & [SUB] & " - " & [HOVR - UDP].[SERVICE],
IIf([MinOfCCT] Is Not Null,"I/O DIST CAB " & [UDP - JC CIRCUITS-05-F].[PWR] & " - CCTS " & [MinOfCCT] & " thru " & [MaxOfCCT],IIf([DESCRIPTIO] Is Not Null,[DESCRIPTIO],"Spare")))

When I have tried to do the same thing with date fields it does not work. The function is fine as long as it is not nested.

That is

Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],"")

and

Rev_col: IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],"")

both work.

But when I combine them into a nested function

Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],""))

I get an error message "! Data type mismatch in criterial expression"

There is no data mismatch in the original tables from which the information was extracted. The dates are all in DATE/TIME and set to "General"format in the tables.

I am wondering if I will have to use Visual Basic function to combine these fields. However that poses a problem. When I've looked at this I find that runtime VB is no longer available in Access and I do not want to go out and buy it on the offchance that it will work.

Can anyone tell me if there is another solution to my problem?

View 4 Replies View Related

Results Formats

May 31, 2006

Hi,

Is it possible to change the resulting format of a new field (expression) within a query?
I.e. Instead of the resulting when running query being displayed results in decimal places but instead being displayed in percentages

Regards

View 3 Replies View Related

Number Formats

Jan 5, 2007

I can't seem to set the format and decimal places to do what I want in my table. I've tried several different combinations of Field Size being either Integer or Long Integer, Format being either General Number or Fixed, and Decimal Places being either Auto or 1.

What I want is to display the number I've input as I've typed it. For example, I type 10.6 and thats what I want.
But instead I'm getting either 11 or 11.0 or 11.00 for all the formatting options I've tried.

Can someone please tell me why its doing this rounding to my number, and what do I need to change?

Thanks.

View 2 Replies View Related

Conditional Formats In Access

Apr 28, 2006

I am having trouble applying conditional formats to my access db they work in excel but cannot seem to put them in to access I have one table that my forms and queries look at

These are the conditions i had used in excel

formula is =AND($H18>=TODAY(),$H18<TODAY()+14)

this turns red if within two weeks of today

formula is =AND($H18>TODAY()+15,$H18<TODAY()+43)

this turns amber if between two and six weeks of today

less than =<TODAY()

text is struckthrough and turned red

Any help with implementing these in access either similar or new method

View 1 Replies View Related

Report Formats Not Saving

Jan 17, 2005

I have built several reports and I have set the layout to Landscape. When I go into the reports after a few days, it puts the format back to portrait. How can I stop this from returning to portrait?

View 2 Replies View Related

Problems With Data Formats And Merges

Oct 7, 2005

Hi

I have been battling to get a mail merge to work correctly with a access database, the data isn't coming across correctly.

I have the date set up in access as a long date format, when it merges into word it changes to 2005-10-05 00:00:00, can anyone tell me what I need to do to get the date to appear correct.

thanks in advance

Di

ps I have read and re read the help pages on this but still can't get it to work

View 1 Replies View Related

Access And Multiple Record Formats

Aug 11, 2005

I need to build a output text file (edi) with 500 byte records and up to 6 different record formats. Can anyone point me to any information/links that would help me understand how to do this?

View 4 Replies View Related

Need A Refresher Course... (variable Data Formats)

Sep 27, 2006

I spent a pretty significant amount of time working with Access in college, but that was years ago. Now that I'm actually trying to do something practical with it in the office, I can't remember how to do anything! This board has been helpful to read so far, and hopefully you can help me with my specific question.In our system, shipping charges by unit vary between customers. I have a table set up with a field associating a customer's charge with the customer's name. The problem is, the two most common charge amounts are $0.25/unit and $0.305/unit (dealing in fractions of a cent here). Right now the field is set to show 3 decimal places, so the numbers are showing up as $0.250 and $0.305 respectively. While this works and all, I want to format the field so that when a currency figure with 2 decimal places is input, it will display only 2 decimal places, but in the event you enter one with three decimal places, all 3 places are shown (and the number is not rounded off).Can anyone help me? My database is in its beginning stages, and I'm still somewhat of a beginner, so the less complicated, the better.Thanks!

View 6 Replies View Related

Queries :: Different Time Formats In Same Column

Oct 18, 2013

I have a query that is drawing info from a union query. There is a time column that is bringing over time in long form (ex 10/17/2013 4:00:00 pm) and just the time (4:00:00 pm). This actually works for me b/c it lets me see which table each piece of data came from but I need a helper column w/ a formula in it to identify it for me. Basically, I need a calculated column that will bring back the word "scheduled" and if it's in short form "actual".

View 4 Replies View Related

Trouble With Report! Images Don't Transfer To Other Formats?

Jan 18, 2005

I have a report that looks like a letter on company letterhead. I wrote a macro that changes the report to .rtf, attaches it to an email so that we can send it to customers. The problem I'm having is that the .jpg logo graphic doesn't show up on the .rtf version of the report.

I've tried using .html and .doc formats and it doesn't transfer to those either. Is there a way to make the graphic stay on the letter?

HELP!

Thanks so much for any ideas.

Beth

View 2 Replies View Related

Access 2000 Report Formats DO NOT SAVED!

Apr 21, 2005

I have one more problem about the version 2000 since it was not happen in version 97. I have changed several report formats margin lots of times (for example .25" for left and right margin to fit on one page) and when I open the reports next time or other users access them from the server (since the application residing on the Novel server) the format keep changing back to the default (1" for all margins: top, bottom, left, right)!!

Please let me know if it is a "bug" in Access 2000 and HOW to fix it!!

Thanks much,

View 1 Replies View Related

Report Formats NOT Saved In Access 2000

Sep 8, 2005

I am having problem to keep the desired format for reports' margin (left, right, top, bottom) and paper size (letter, legal), and orientation (portrait, landscape) stay!!

Currently, I have one MS Access 2000 database application resides on Novel Netware server version 6.0, [B]although I open the Access database exclusively to change all my reports design to the way I like, and SAVED them properly; however, later when I and other users view those reports, the formats change back to default view, which are "letter size, portrait and all margin set to 1")??

It is very STRANGE and FRUSTRATING and incovenient since all users have to manually go to "Page Setup" of the report's design everytime to change margins to make the data display nicely on one page!! It has never happened like this in version '97!

Plllease let me know if ANYBODY has any SOLUTIONS to this bug of version 2000 of Access?? Or any UPDATE file released to help get rid of it??

Thanks so much,

View 1 Replies View Related

Reports :: Print Two Different Formats Based Upon Value Of A Field

Dec 20, 2013

FoodStamps This is a YES/NO field (printed in the first or second columns).FSAmount This is a numeric field(always printed in the third column).I have a preprint form with three columns.The first column is for a YES answer and I want to print YES if FoodStamps is TRUE.The second column is for a NO answer and I want to print NO if the FoodStamps is False.The third column is either blank if the first column is YES or the actual value if the second column is a NO

Two cases:

If FoodStamps is TRUE then print

YES Blanks
I have a preprinted form and I want the YES in the first column and blanks in the third column on the form regardless of what is in the fieldFSAmount

If FoodStamps is False then print
NO value in FSAmount

the NO in the second column and the value of what is in the FSAmount field printed in the third column.I tried to figure it out with conditional formatting with no luck.

View 1 Replies View Related

Tables :: Multiple Data Entry Formats

Mar 26, 2014

I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.

In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.

Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?

View 3 Replies View Related

Reports :: Printing Statements For Clients - Different Formats Within Same Report

Aug 5, 2015

I am printing statements for clients. Depending on certain criteria about the client, the format of the statement changes completely. This is just how the boss wants it, I have no control. I still want to generate all statements at one time (not separate reports).

I currently have this working using multiple subreports, and changing their Visibility property, only one being visible at any one time. This works, but it is very slow, I assume because I am basically making Access do triple the work (I have 3 subreports).

I pretty confident this is not the intended use of subreports. Is there a "correct" way to do what I am doing? I know I could just manipulate everything with VBA, but having subreports makes editing the different formats very easy.

View 10 Replies View Related

Modules & VBA :: Error When Apply Condition Formats On Excel Sheet (from Access)

Oct 14, 2014

I am pushing some data to Excel from an Access query. When the data is in Excel I reformat the sheet by changing the fonts, applying borders and cell formats - I have got all of this to work fine.

The one thing I am struggling with is applying conditional formats. I am pretty sure it is something to do with incorrectly referencing the applcation/sheet. An extract of what i think to be the key parts of the code are below.

....
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
...

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

[Code] ....

View 8 Replies View Related

Nested If (I Think)

Sep 9, 2005

Hi

I have just designed a central training database to aid in the running of our training department.

One section is for an IT course which contains 8 modules.

The tests have the following IDs

Mod 1: ID05, ID06, ID07, ID08
Mod 2: 2.1, 2.2, 2.3, 2.4
Mod 3: 3.1, 3.2, 3.3, 3.4
Mod 4: 4.1, 4.2, 4.3, 4.4
Mod 5: 5.1, 5.2, 5.3, 5.4
Mod 6: 6.1, 6.2, 6.3, 6.4
Mod 7: 7.1, 7.2, 7.3, 7.4
Mod 8: 55, 56, 57, 58

One person may take any of the tests.

If someone completes Mod1, 2 and 7 they achieve Level 1

Similarly if someone completes Mod 3, 4, 5, 6 and 8 they achieve Level 2.

I need to run queries to find who has completed L1 or L2 or both.

Obviously they only achieve if they pass so in that table there is a status field with

Pass
Fail
Wait

As the options

Queries have never been my strong point and I was hoping someone here could help me.

I’m using XP Pro, with Office 2003 and the office 2000 db format.

Many thanks

Dazzy

View 1 Replies View Related

Nested IIF

Oct 26, 2006

Hi

how would one go about nested IIF

New Sal : IIf([emplsalaryaa]>80000,([emplsalaryaa]+([emplsalaryaa]-80000)/(100)*9)+7713.98)

i have the above but i need to put one more

iif

i have tried , IIF but it does not work

i don't know why?

Asad

View 3 Replies View Related

Nested IIF

Jun 8, 2007

My first post ever... so here goes.

My Goal:

Columns of data contain date fields & "Status" that indicates where a case lies. I'm trying to calculate days spent in a specific area (determined by status code) and total amount of time spent there all together. Status code begining w/ "I***" means case was sent to an area, w/ "R***" it returned. I have it together up to a point. My problem lies with end of expression where I want to calculate last date at specific location to now. In line 1 it would be from 5/03/07 to now since no r162 follows & blank field follows. Problem is with second IIF - it acts independently instead of giving false value from first IIF... i think.

status1 date1 status2 date2 status3 date3 status4 date4
1) I162 5/3/07 I004 5/20/07 I006 6/1/07
2) I162 4/2/07 R162 4/30/07

My failure:

stat2n4: IIf([status2]="i162" Or [status2]='i062' Or [status2]='i009' Or [status2]='i159' And [status3]<>'r162' Or [status3]<>'r162' And [Status4]="r162",DateDiff("d",[date2],[date4]),IIf(IsNull([status4]),DateDiff("d",[date4],Now())))

View 9 Replies View Related







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