Creating a Global Variable and passing it through querires

[daniel1676]

Post 20 has the newest code

This is a mixture of both modules and quering

I want to create a global variable that is then passed through querires to make sure weekends are skipped.

I have code ready, all that i need to know is how to create a variable and also how to change its value in an IIF statment (variable has to be an integer)

(using access 2003)

 

[Galaxiom]

Create a function that returns the value of the variable.

 

[daniel1676]

i have added this code

Option Compare Database
Option Explicit
Public mycheck As Integer
Function datecheck() As Integer
datecheck = mycheck
mycheck = 1
End Function

but when i try to use daecheck() in a query it says that it it undefinded

 

[Galaxiom]

Needs to be in a Standard Module.

 

[daniel1676]

Needs to be in a Standard Module.

isn't that what i have already done.

If not how do i put it into a Standard Module

 

[PeterF]

Has the module the same name as the function?

 

[Galaxiom]

The first module we encounter is the Access Class Object Module.

The Standard Module is different. Look at the Project Explorer in the view menu of the VBE. Right click and choose Insert.

 

[gemma-the-husky]

i was going to comment on this. Your intial premise about "change it in an iif statement" is curious

let's look at what you are trying to do - you need a query, that takes an argument of some sort to filter the items returned

so basically the SQL is "select fields form tables where somefield = somevalue"

so - how do you get the test - "somefield = somevalue" to work.

a few ways.

1. Hard code it in the query. Ok for ad-hoc queries, if you have full access to the database - but often we don't, and it is more user-friendly to get the value interactively
SO:

2. Get the value from a form text box

3. Get the value form a global variable stored in your database

Methods 2 and 3 are pretty similar. I prefer to use the variable method, as the code becomes more reusable, but many go the other way.

Now you can test the variable in an iif statement, to decide how to react - but I don't see how or why you would want to change the variable. Is your terminology slightly off? or are we not quite understanding what you are trying to do?

 

[daniel1676]

to make it clear this code will be copied to 4 more querires, thats why confirmdate() is needed

spent some time on this and my code now looks like this:

IIf(
Weekday(Date()+confirmdate() )<>1 And Weekday(Date()+confirmdate() )<>7,
Date()+confirmdate()(*),
IIf(
(*)Weekday(Date()+confirmdate() )<>1 And Weekday(Date()+confirmdate() )<>7,
Date()+confirmdate()(*),
(*)Date()+confirmdate()
)
)

confirmdate() is a function i made which is an integer. Its job is when weekday() = 1 or 7 it adds 1 to move to the next day without displaying saturday and sunday (this is once it is a report)

Question:
i want to increase confirmdate() by one at the end if the IIF is true(*) are at the start if the IIF(*) is false when i add e.g. confirmdate()+! i get an error.

 

[gemma-the-husky]

I see

I would try and encompass all this within a function - rather than use in line nested iifs. far easier to write and debug

function calcdate(with parameter if you likes) as date
   do stuff to evaluate calcdate
end function

then in your query you can just add a column

evaluateddate: calcdate()

or with an argument

evaluateddate: calcdate(somefield)


note that rather than using explicit numbers, you can use vb day constants. Much easier to identify what the code does.

if weekday(date+addon)<>vbsaturday and weekday(date+addon)<>vbsunday then
  etc
end if

 

[daniel1676]

Thanks for the help so far, here is what i have done so far:

Option Compare Database
Option Explicit
Public mycheck As Integer
Function confirmdate() As Integer
mycheck = 1
confirmdate = mycheck
End Function

Module 1, used to keep track of what day is being displayed in the query

Option Compare Database
Option Explicit
Function calcdate() As Date
If Weekday(Date + confirmdate()) <> vbSaturday And Weekday(Date + confirmdate()) <> vbSunday Then
Weekday (Date + confirmdate())
mycheck = mycheck + 1
Else:
If Weekday(Date + confirmdate()) = vbSaturday Then
mycheck = mycheck + 2
Weekday (Date + confirmdate())
Else:
mycheck = mycheck + 1
Weekday (Date + confirmdate())
End If
End If
End Function

Module 2 created with the above advice, not sure if the mycheck should be confirmdate() instead, tryign both out with each attempt

In the query Criterai box

evaluateddate: calcdate()

***********************************************

On running of the query am getting error

The expression you entered has an invaild . (dot) or ! operator or invaild parentheses

Any advice

 

[gemma-the-husky]

I am not sure what you are doing exactly, but this is not quite right

calcdate will return a date . if you are comparing it with an existing field, then the criteria just needs to be =calcdate()

if you want to see the calculated date, then put is as an extra columnn in the query

=calcdate()

will be automatically replaced by expr1: calcdate(), on closing the query.

and this is what you can rename eg mydate: calcdate()

 

[daniel1676]

just notice that myself and was just about to change my post

just working on getting the code to work

(you can tell how nooby i am at access)

 

[Galaxiom]

The two functions seem to fall over each other and I am sure the whole structure could be simplified. I think you have gotten a bit bamboozled in the code (I know it happens to me). Take a step back and revisit the underlying logic. The goal often becomes clearer the next day after a break.

The confirmdate() function is not needed unless you want to use it in a query. ConfirmDate could simply be instanced as a variable in the Declaration section of the module.

Calcdate is used correctly because it is meant for a query. However I would be inclined to give it a more meaningful name. CalcDate could mean anything to you in a few months and to anyone maintaining your code.

Note that one module can hold multiple functions and it is not necessary to put each one in a separate module. Indeed if a function is only used within a Class Module (such as a Form module) then it can be included there.

Please also post code indented inside a code box and indent it for readability.

 

[daniel1676]

Ok this should be the last bit of help i need:

The code goes
Weekday (Date + confirmdate())
(this returns a number, today being Thrusday it returns 6, for friday)

am guessing weekday will need to be changed, i have tried replacing this with Date but i get errors,

can you post any ideas you have, am i have a few ideas am going to try myself

*****************************************************
just tried removing weekday, this works in debug.Print (in the Immediate window the date is shown Debug.Print Date + confirmdate())

when i remove the Debug.Print the + sign is also removed (it is automatically removed) i tried placing the whoel line in brackets but i get an error

 

[daniel1676]

@GalaxiomAtHome

will take on board what you said about the code box, sorry

about maintainability etc. This is a first year uni project and am not planning on coming back to it and also since it is a relatively small database it would only take a few moment for me to understand it. (This isn't a rant or anything to be insulting its just i hate databases and the soon its over the quick i will be finished my first year 13 week summer)

 

[daniel1676]

just discovered another problem. When change the value of mycheck its value is reset.
I know the problem, its due to me stating its value in the function

Function confirmdate() As Integer
mycheck = 3
confirmdate = mycheck
End Function

when i move mycheck =3 outside the function i get an error.

so my tow remaining problems are that and what i have already stated (copied below)

just tried removing weekday, this works in debug.Print (in the Immediate window the date is shown

Debug.Print Date + confirmdate())

when i remove the Debug.Print the + sign is also removed (it is automatically removed) i tried placing the whoel line in brackets but i get an error

 

[Galaxiom]

Weekday (Date + confirmdate())

This is a one sided expression so doesn't actually do anything except run the confirmdate() function.

 

[daniel1676]

got any idea on how to modify this line to get the date instead of a number?

 

[daniel1676]

UPDATE ON CODE

Option Compare Database
Option Explicit

Dim showthisdate As Date

Function calcdate(showthisdate) As Date
If Weekday(Date + confirmdate()) <> vbSaturday And Weekday(Date + confirmdate()) <> vbSunday Then
showthisdate = DateValue(Date + confirmdate())
mycheck = mycheck + 1
End If

If Weekday(Date + confirmdate()) = vbSaturday Then
mycheck = mycheck + 2
Weekday (Date + confirmdate())
End If

If Weekday(Date + confirmdate()) = vbSunday Then
mycheck = mycheck + 1
Weekday (Date + confirmdate())
End If
End Function

(this is just focusing displaying the date in the query then am going to look at the global variable changing)

This function is then called into a query stating in the criteria
calcdate(showthisdate)


Nothing is being passed out

Any ideas