Conditions in macros
Can anyone let me know why Access insists that conditions in macrosdon't seem to support comparitive operaters for checking ranges, andonly support the Between.. And operators? I had a whole set ofconditions where some were checking between a range of numbers (suchas Age). Where I had 20<=Forms![Form1]![Age]<25, it was ignoring thecondition, but it did work when I changed it to (Forms![Form1]![Age]Between 20 And 25).Cheers,Chris For compound conditions, use this kind of construct: ([Forms]![Form1]![Age] >= 20) AND ([Forms]![Form1]![Age] <= 25)-- Allen Browne - Microsoft MVP...Incorrect Workers' Comp posting date
When posting Worker's Compensation Tax for payroll, the wrong posting date
was used (03/28/09 instead of 02/28/09). This hasn't been posted to the GL,
so the payroll module is the only thing we're worried about.
Is there a way to void this posting to payroll and start over or change the
dates to correct this?
If I am not mistaken, it does not actually post to the Payroll Side, just
delete the gl batch and run it again with the correct date.
> When posting Worker's Compensation Tax for payroll, the wrong posting date
> was us...how can i apply conditional formatting by code ?
Say that there is a table T_PERSONAL [Name (text), Surname (text),
I created a Tabular Form (F_PERSONAL) which retrieves information
I want to put a image for a specific record on the form, if the
satisfy some specific criteria. For other record which does not
satisfy the criteria, image should be invisible.
Say that, if [Name (text)] ~text13~ is "ALBERT", Image Object
~Image13~ should be visible. I dont suppose that Image Object should
be included in T_PERSONAL. It will be inserted manualy during design
stage of Tabular Form.
I would not as...file format needs to be changed
I have an excel file which comprises of a template for Identity card with a
jpg file embedded in it. I would like to save this file in a jpeg format so
as to get a photo printout of the same. How is this possible?
If you scan the document, that would put the file into an image format.
Then you could convert the file to whatever image file format you wanted.
Just a thought...
Highlight the area you want printed then hold SHIFT key and select Edit>Copy
Open your favorite graphics editor or MS Paint and paste to there.
Save as *.jpg
Gord Dibben Excel MVP
On Tue, 14 Jun 200...Converting a string date field/node in an XML document to Date type in a dataset
I am having problems with Date Strings when using VS.net method of
converting an XML document to a dataset.
I need the the date nodes to be of date type. As I am loading the
Dataset to a datagrid, and I using the sort facility to sort by date.
Currently the dates are being treated as strings.
email@example.com (ree32) wrote in message news:<firstname.lastname@example.org>...
> I am having problems with Date Strings when using VS.net method of
> converting an XML document to a dataset.
> I need the the date nodes to be of date type. As I am loading the
...date format #18
I have a spreadsheet containing lots of pivot tables. The source data
contains date formulas in the following ways:
=IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11
=IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07
As a result, when I try to sort my pivot table data by date, the dates that
are shown as Nov-07 are recognised as text therefore only allowing me to sort
by alphabetical order. The dates that are like 2007-11, i am able to sort it
by the earliest/latest dates.
I...Conditional Printing of Certain Fields in A Report
i need to have a conditional printing of some fields (SEE BELOW)
VENDOR SHARE $999.99
LESS TAX $999.99
tax (%) 20%
AMT DUE $999.99
how do I print only these 5 lines when the tax amount is not zero.
Also how do i combine line 2,3 & 4 and looks like
LESS ENGLAND (20%) TAX
I tried doing the following:
="LESS" & [COUNTRY] &"(" &[TAX (%)] &")"
in the control source but i got a #ERROR error
Any help would be appreciated.
Kevin T <Kevin T@discussions.microsoft.com> wrote:
>i need to...Question on Sorting by (date, etc)
I have a column (A) which sre dates.
If I select the column heading and click either the sort ascending or
desending button, the date column alone sorts independent of the other eight
columns that comprise the entire entry.
How would I go about being able to sort by a particular column and have
everything sort with it?
I have other columns that I would like to sort by sometimes too, like
"color", "type", etc.
Thanks for reading!
select all the columns and now go to data | sort .
On Feb 20, 11:21=A0pm, "Ed" <2...@333.com> wrote:
> I have a column...formatting #4
Hi, is it possible to automatically display those cells with a formula with
a different format. Eg I would like 'input' cells in green, and
'calculated' cells in red. Thanks.
Change color numbers to suit
Dim c As Range
For Each c In Selection
If c.HasFormula Then
c.Interior.ColorIndex = 4
c.Interior.ColorIndex = 6
Microsoft MVP Excel
"KRK" <email@example.com> wrote in message
> Hi, i...Copy worksheets and formats
I have a worksheet (budget) set up as 31 columns representing days of a
month. I also have a worksheet designated as yearly, 12 columns for each
month. The first worksheet, Jan, totals numerous rows and sends the data to
the Jan column of the monthly WS. I need to copy the Jan worksheet in a way
it will change the Jan! to Feb!, then Mar!, etc. If I copy it as is the data
will be the same as the month of Jan. Hope you can understand this. I can't
More than one way, but an easy way if each month formulas are the same is to
copy Jan sheet, and paste them into the Feb a...Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data
refreshed from the Web. However I can't get it to show up on a chart
with dates on the X axis. Have the axis formatted OK, but how does it
collect its data?
Have tried a simple worksheet with manually entered data & the dates
entered in column A, but still can't get a chart to work.
Jon Peltier has a charting tutorial that may help you get started:
When you manually enter data and dates, what problems do you have with
>...using date function, month shows as January when i type (12)
when date of birth is A1 and i use the formula
=date(year(A1)+19,month(12),day(31)), to get the last day of the year before
the 20th birthday, 31/1/1919 appears instead of 31/12/1919. why is this? if
i take the +19 away, the same thing happens. The only time it works is if i
specify the year instead of using another cell.
Does this formula do what you need?
The reason your original formula did not work is because the MONTH()
and DAY() functions require serial date numbers (that is to say, an
Excel date equivalent expressed as a number).
=DATE(YEA...how to replace the date
i have put the date 03/03/2004 in a worksheet in various cells, i woul
like to replace that date with 03/04/2004.
pls help me
Message posted from http://www.ExcelForum.com
try the following
- goto 'Edit -Replace'
- enter your existing date and your new date
P.S.: please stay in the original thread
P.P.S. My first name is Frank :-)
> hi kabel,
> i have put the date 03/03/2004 in a worksheet in various cells, i
> would like to replace that date with 03/04/2004.
> pls help me.
> Message p...How do I sum dollars in one column based on dates in another?
How can I sum the total numbers contained in one column based on dates in
Example: I need the total of 5 units sold int the month of January 2006. I
have the sale ammounts in one column and the closing dates in another.
What forumla would I use for this?
Thanks in advance for any help.
change 1 in the month part to a different month
Northwest Excel Solutions
(remove ^^ from email address)
"Alan" <Alan@discus...Validate Purchase Order Date
We were able to enter a PO (against a Project) dated 2004, several years
before we started on GP, so I'd guess that (because this is non-posting) the
date isn't validated against the fiscal periods that have been set up.
However, this lead to a Project Begin Date of 2004, and caused an error when
we tried to enter a fixed fee amount against the project "Fiscal information
does not exist for the date range. The fiscal year must exist in order to
create the periodic records". The only way I found to fix this was to go
into SQL table PA01201 and fix the Project's b...user form
I am so frustrated. I am creating a form and I want to force a phone number
format. Easy in Excel but cannot find it anywhere in word 2007. Please help
Use the VBA Format function
Format(1234567890, "(###) ###-####")
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Dcook" <Dcook@discussions.microsoft.com> wrote in message
news:5F0E1C93-E828-46F7-98EF-FE6DF84...E-mail Templates HTML format with pictures
Is it possible to create custom templates with an HTML body, including like
my company logo ?
If this is possible, can any one help me..
Thank you again in advance
I created a web page in MS FrontPage. Posted the page on a web server with a
public address. Then copied the page into a Direct Email Template. If you
don't require a logo you can just paste it from FrontPage right into the
Direct Email Template.
"Ulrich" <Ulrich@discussions.microsoft.com> wrote in message
> Is it possible ...Calculate difference between 2 date and times with average
I have used the formula:
Start time End time
6/9/2007 10:35 AM 6/9/2007 3:30 PM 4:55
Formula Description (Result)
But when I use the text function, I cannot calculate an average. Also, if
you know a way to calculate the difference and exclude weekends...
As for excluding weekends, investigate the NETWORKDAYS formula in Help.
But if your workday is not 24 hours long, and you have holidays to contend
with in addition to weekends, the problem is fairly complex.
Here are the steps:
1. If StartDate and EndDate are on the same date, hours worked = number of
hou...Query Date Help W/Avg work days
please note that in order to get the date 6/28/1979 I am using average work
days each month of 21.75
I have tried to Add just the total days to the date but it's not correct
because I need it to be based off the 21.75 days per month not actually days.
Total Years of Prior Service Days Hire Date Adjusted Hire Date
7 years 4 months 6 days 2685 6/15/1987 2/7/1980 (s/b 2/9/80)
1 year 8 months 13 days 620 2/10/2005 6/1/2003 (s/b 5/27/03)
This is what is being calculated so far once I run the query.
> This worked great thanks so much!! I have ...Workflow - date and time
I have a field called enrolment date which is usually lesser than the
created on date.
i want a workflow which says, when a new contact is created wait for 5 days
after enrolment date and then create - 1st mailer task
wait for 2 months and create - 2nd mailer task.
i have got a workflow and it says
when a new patient is created
wait 5 days after contact.enrolmentdate
if case 1
create mailer 1
if case 2
create mailer 2
however, both mailer tasks get create right away rather than waiting for 5
days and 2 months. in the wait for timer i ticked revaluate expressision whe...Conditional formatting #50
The worksheet I've been working on uses paste links to carry forward
workers details from one worksheet to the next. These cells are left
unprotected so the chain can be broken if a person leaves, and someone
new has to be inserted.
This is fine but it makes it tricky to spot where breaks occur. Can
conditional formatting tell the difference between a formula and text
entered? If so can someone please give me an example that I can use.
You would need to build a simple UDF
Function IsFormula(rng as range)
IsFormula = rng.HasFormula
and use t...Conditional copy cells from one excel file into current
Does a macro (or function or ??) exist to conditionally copy value
from a stored spreadsheet into the current (open) spreadsheet?
Here's the scenario:
Stored spreadsheet is two columnsof text (~200 rows) : Think explici
question in column one, then the correct answer in column two of th
Then my current (open) spreadsheet has SOME of the same questions i
I would like to invoke a [macro?] that, in pseudo code, does somethin
similar to the following:
for each row (question in column 1) of the current spreadsheet
Search in column 1 of *stored* spreadshee...Adding Dates
I have a field with arrival date then a field with days and another field
with departure date. I enter a date in the arrival date field, then I put in
how many days as a number in the days field and using a Macro command on
exit I want the departure date to show automatically the arrival date plus
How do I do it ?
On Mon, 28 Jan 2008 19:58:48 +0200, Ange Kappas wrote:
> I have a field with arrival date then a field with days and another field
> with departure date. I enter a date in the arrival date field, then I put in
> how many days as a number in the days fiel...Date and hour arrangement
Apperiacite for your help,
I have list of date and hour with below format
dd.mm.yyyy hh:mm:ss am/pm
for dd.mm.yyyy is belong to hr 00:00:00,
so my question here, i'm using this queries to get hourly;
Hour: IIf(Trim(Mid([PERIOD_START_TIME],12,2)) Is Null,"00",Trim(Mid(
this queries is to get, when "dd.mm.yyyy" put 00, else put hh, but seems like
wrong queries, due to result is still NULL for non hh, but with hh is working
please correct me.
Message posted via http://www.ac...How to copy custom number formats
I have a custom number format for a Excel Cell A1 which is #,##0.00
and in A1 I now have a value 1000 CAD.
I have assigned cell B1 as =A1 but an external application when the
report is run populated now cell A1 as 20000 MXP and now my cell B1
says 20000 CAD and not 20000 MXP and when I check the format for B1 it
still as the format as
#,##0.00 "CAD" whereas now the format of my cell A1 is #,##0.00
"MXP" appears only the value and not the format is being copied.
Can any of you suggest a workaround to resolve this problem.