Replies

Hi Marc – Try this ….
=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))

Just a question and certainly not a correction, but shouldn’t we be using the vba.date(vba.year(vba.today….etc? to make this global?

Just a question?

Thanks,
Darryl.

I didn’t notice any mispells, the problem stems from the variable string, which I think was a mistake on my part. The special notation ‘ for some reason needs a valid date variable imo. Once I changed the variable to date, and gave a two variable which worked.

Dim xdate1 as date
Dim ydate2 as date

xdate1=(00:00:00)
ydate2=the date value in the format year/month/day format.

you need to keep the special notation I am guessing cause it worked fine from that point on, if you add a calander control you can access a database and fire it when you click on the calander.

Thanks Rory. I didn’t think the problem was in the variable.

Yes I did, and I got the error , 1004 This operation is not available for extrenal Database.

And Thank you for you effort.

oops…I meant the captial S, see how it didn’t change itself to a capital, if you declare the variable as a capital S, doesn’t see the s, and it keeps it small, I think the ‘ symbol is key to my problem.

rory, thanks for your reply

if you take a look at the variable, you will notice that in declaration there is a B S….but in the code it doesn’t see it.

Option Explicit
Sub Macro1()
Dim sYear As String
Dim sMonth As String
Dim sDay As String
sYear = “2011”
sDay = “13”
sMonth = “06”
Range(“A1”).Select
With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DSN=VPC – PROD;Description=VPC – PROD;UID=datalink;PWD=datalink;APP=Microsoft® Query;WSID=VPC-PMXP-PLT1;DATABASE=VPC” _
, Destination:=Range(“A2”))
.CommandText = Array( _
“SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time” & Chr(13) & “” & Chr(10) & “FROM VPC.dbo.plate_flow plate_flow” & Chr(13) & “” & Chr(10) & “WHERE (plate_flow.line_number=’1′) AND (plate_flow.scanned_time>{ts ‘ syear-smonth-sday 02:26:48” _
, “‘})”)

Option Explicit
Sub Macro1()
Dim sYear As String
Dim sMonth As String
Dim sDay As String
sYear = “2011”
sDay = “13”
sMonth = “06”
Range(“A1”).Select
With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DSN=VPC – PROD;Description=VPC – PROD;UID=datalink;PWD=datalink;APP=Microsoft® Query;WSID=VPC-PMXP-PLT1;DATABASE=VPC” _
, Destination:=Range(“A2”))
.CommandText = Array( _
“SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time” & Chr(13) & “” & Chr(10) & “FROM VPC.dbo.plate_flow plate_flow” & Chr(13) & “” & Chr(10) & “WHERE (plate_flow.line_number=’1′) AND (plate_flow.scanned_time>{ts ‘ syear-smonth-sday 02:26:48” _
, “‘})”)

Thanks for the help, I would like to know, since I am dealing also with decimals, like 8.25, what variable should I use? I keep losing the .25 when it converts the number over, should I avoid single and double?

Thanks dude,

I tried a few variations of the code, I have never passed an object with an argument.

Thank you kindly.

HUH:)

Oops that not true…I have, but I haven’t used it as the argument.

Is this a legal function?

Any one?

Public Function TestAreAnyTrue(ZX As Collection) As Boolean
Dim ctl As Control
TestAreAnyTrue = False
For Each ctl In ZX
If ctl.Value = True Then
TestAreAnyTrue = True
End If
Next ctl
End Function

Hey Rory,

Thanks for your reply,

You gave me that code about 4 years ago:)

Can you tell me why I can’t get this to work?

Passing ZX would be an arugment like Userform1.frame1.controls?

I have about 10 different frames with option buttons and I thought if I could modify your code to possibly handle arguments.

Public Function TestAreAnyTrue(ZX As Variant) As Boolean
Dim ctl As Control
TestAreAnyTrue = False
For Each ctl In ZX
If ctl.Value = True Then
TestAreAnyTrue = True
End If
Next ctl
End Function

Thanks Hans.

Thanks Hans,

I’ll take your advice on removing the -1, from the (plate), because I am using the split function on the Arrparts, it has produces ” ” elements in the Array, so I’ll keep the -1 when filling the array.

Thanks,
Darryl.

Thanks Hans,

Do you already have a list of names? on a worksheet?

Are you trying to accomplish in code?

Thanks,
Darryl.

If then number of 4s increases/decreases as the column width is altered, then I suspect a number format involving an asterisk.

You guys really are experts!

I changed the format of the cell to General, and input the value and it is ok now,

Thanks,
Darryl.