Hi everybody,

I'm new to using VB for Access, know more about Excel. I'm working on a solution and have hit a few road bumps. Any suggestions are most welcome.

1. What is the best way of comparing two dates and setting a flag if more than one day has elapsed? I've done this in Excel, not sure about Access
2. What process can I adopt to use a Combobox to open a Form, (or several depending upon the selection)?
3. I'm trying to transfer a process from Excel into Access. Basically I have a matrix containing prices. Using the two axis values I can locate the required price. How would this be accomplished in Access?

Thanks in advance for any assistance.
Chris

Hey,

1. You can compare the two dates using "DateDiff". You may want to doa search on this to see the syntax. Basically its:

If DateDiff("d", [StartDate], [EndDate]) > 0 Then
   etc...
End If

If you are putting it into a query, you can add a column by entering the DateDiff formula in the row source:

Flag: DateDiff("d", [StartDate], [EndDate])

Then run your flag off this field.

2. Create a combo box that looks up the values you want to use to pass to your forms. Then grab that value and either pass them to a form, or check them and open the proper forms. That might look like:

Button_Click event
Dim inValue As Integer
Dim stLink As String
Dim stDocName As String

If IsNull(Me.ComboBox) or Me.ComboBox = "" Then
   'error message
   Else
   inValue = Me.ComboBox 'assumes PK is numeric
End If

'Then check the value against records if you want to open a particular form, then pass the DocName and run a DoCmd to open your form or report.

3. Can you post more data on the excel to access issue? Basically you deconstruct the pivottable (matrix) such that:

Person Jan06 Feb06 Mar06 etc
Tom 1 2 1
Joe 3 1 1
Mark 1 2 3

Becomes:

Person Month Value
Tom Jan06 1
Tom Feb06 2
Tom Mar06 1
Joe Jan06 3
etc...

HTH

BTW, please post in Microsoft Office>Access VBA


mmcdonal

Thanks so much, this was really helpful. As for the Excel issue. I have two selections, the height and width of a door ... When you input a size for the door height it looks up the next highest value and selects that row. You then enter the door width and it looks up the next highest value for that column. Using an index fuction, I can then pull the data from the matrix. What I to do now is to replicate this function in Access. The basic idea you proposed seems a good starting point. I will also move over to the Access VBA forum.

Thanks again
Chris