Does MS Access has functions like Excel? If sunday move to monday

capsula4

Registered User.
Local time
Yesterday, 16:03
Joined
Jan 3, 2008
Messages
122
Hi!

I would like to know if MS Access can handle functions that Excel has, for example, "weekday" function, that returns a number representing the day of the week (sunday=0, monday=1, ...), given a date value.

If so, I have a conditional like this in excel:
=IF(weekday(DATE-3)=0;DATE-4;DATE-3)

If DATE is sunday, then move to monday. Is there a way to make something like this in Access?
 
I would like to know if MS Access can handle functions that Excel has, for example, "weekday" function, that returns a number representing the day of the week (sunday=0, monday=1, ...), given a date value.
Yes there is. The function is called Weekday(). It returns an integer value (0-7), representing the day of the week that corresponds to the date that is evaluated.
If so, I have a conditional like this in excel:
=IF(weekday(DATE-3)=0;DATE-4;DATE-3)

If DATE is sunday, then move to monday. Is there a way to make something like this in Access?
Yes, of course there is, but I'm not sure you've given enough information about your situation to get a good answer. If you would like help on this, could you explain a bit more about what you are doing?

>>>For reference<<<
Functions to look at: *Weekday(), *WeekDayName(), *IIF(), *Switch()
 
Yes there is. The function is called Weekday(). It returns an integer value (0-7), representing the day of the week that corresponds to the date that is evaluated.Yes, of course there is, but I'm not sure you've given enough information about your situation to get a good answer. If you would like help on this, could you explain a bit more about what you are doing?

>>>For reference<<<
Functions to look at: *Weekday(), *WeekDayName(), *IIF(), *Switch()

Thanks a lot Adam!

I have one doubt, is IIF with two "I" or it was just a mistake?

And the other thing was just, if a date was sunday, then I wanted to move it to monday, like this:

IF(weekday([DATE_OF_TRANSTACTION])=0;[DATE_OF_TRANSTACTION]+1;[DATE_OF_TRANSTACTION])

considering sunday=0 and monday=1

Another thing, is a page or place where I can see all the functions available on ACCESS?
 
I have one doubt, is IIF with two "I" or it was just a mistake?
No, an IIF statement is completely separate. You have written correctly, but the function starts with IIF, not just "IF".
And the other thing was just, if a date was sunday, then I wanted to move it to monday, like this:

IF(weekday([DATE_OF_TRANSTACTION])=0;[DATE_OF_TRANSTACTION]+1;[DATE_OF_TRANSTACTION])

considering sunday=0 and monday=1
The above code is OK capsula. There is only one problem though:

If the weekday evaluation = 0, the date's VALUE will have 1 added to it, not the Weekday() function's value that was assigned in the IIF function's evaluation. So, watch out for that. To see the number "1" for the [DATE_OF_TRANSACTION], the statement will look like this:
Code:
IIF(weekday([DATE_OF_TRANSTACTION])=0, 
[COLOR="Red"][B][U]weekday[/U][/B][/COLOR]([DATE_OF_TRANSTACTION])+1, 
      [COLOR="Red"][B][U]weekday[/U][/B][/COLOR]([DATE_OF_TRANSTACTION]))
Another thing, is a page or place where I can see all the functions available on ACCESS?
To get most of the functions (or maybe all of them, I'm not sure), type this into the help menu: Functions (arranged by category)

And BTW, there is no "0" in the Weekday() function value list. :)
 
IIF works in queries. When you get into VBA it becomes IF.
 
Thank you Adams!! I actually noticed about the change of the date was affecting the date, not the weekday, that is what I was actually wanting to do.

And also thanks Neil for the tip! I'll have it in mind
 
I'm putting the following as Default Value, but isn't working:

IIF(weekday([DATE]-3)=1;[DATE]-4;[DATE]-3)

it appears an error saying that the syntax is invalid, that I may be missing inverted commas (" ") for text... do you know how should I make this?

Another lil thing, I'm using Spanish version of Access, and I'm not sure if I should use the spanish names for the logical operators/functions.
 
What is [DATE]? Date is a reserved word in Access as there is a function Date() that returns the current date. Is that what you want to use?

Are you putting this as the default value for the field in the table? It won't work.

In the English version of Access you use commas "," to separate the parameters in an IIF statement not semicolons ";"
 
I'm actually using [DATE OF TRANSACTION] i just wrote here [DATE] to make it faster lol!

And yeah, I'm putting this as the default value of a field in the table. I need this to be a default value and not a calculated field in a query since it's a default value which may require a different value. So isn't this possible?
 
You can set it as a default value in a form, but not in the table.
 
You can set it as a default value in a form, but not in the table.

Thank you neileg!! Although is a way to still store that field using a form? I just should make a "date of transaction" field in the table and set the default value in the form instead of in the table? :confused:
 

Users who are viewing this thread

Back
Top Bottom