Query to populate list

[Guest]

I have form which searches for available days from db. Db contains tables for
6 month with fields named 1-31(or 30 depends on month). In the form I put
date1 (ex. 12/06/2005) and date2 (ex.14/06/2005). The code should find which
of those days are available (table contains something else than 0).

Following picture's first line shows a case where all those days are
available(0)in table. Def-field contains autonumber.

def 12 13 14
1 0 0 0
2 1 45 0

Now I want to run simple query which returns "def" field's value to
list-control "List" in the form named "Form1" if all the days contains "0".
So in this case the list would contain 1 (see picture). The query has to be
run through VBA, because the table and field names in query cannot be fixed.

I'm not sure if it was clear enough, but you are free to ask more information.

 

[Guest]

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect
'Stop

Me.list.RowSource = strSqlSelect
Me.list.Requery

End Sub


Also Note: Here is an example of how to call it.

Private Sub TestLoadListBox()

Call LoadListBox("tblMonth2", , , , , , , , , , , , True, True, True)

End Sub

This will set the forms listbox named "list", rowsource to show all of the
defs (Autonumbers) from that table where fields named 11, 12, 13 are equal to
0 (zero) now if you are looking at null values or some sort of text values
other than zeros you will need to change the code accordingly. I know it
isn't pretty but neither is this design! LOL... Take Care & God Bless -- ~
SPARKER ~

 

[Guest]

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect
'Stop

Me.list.RowSource = strSqlSelect
Me.list.Requery

End Sub


Also Note: Here is an example of how to call it.

Private Sub TestLoadListBox()

Call LoadListBox("tblMonth2", , , , , , , , , , , , True, True, True)

End Sub

This will set the forms listbox named "list", rowsource to show all of the
defs (Autonumbers) from that table where fields named 11, 12, 13 are equal to
0 (zero) now if you are looking at null values or some sort of text values
other than zeros you will need to change the code accordingly. I know it
isn't pretty but neither is this design! LOL... Take Care & God Bless -- ~
SPARKER ~

I have form which searches for available days from db. Db contains tables for
6 month with fields named 1-31(or 30 depends on month). In the form I put
date1 (ex. 12/06/2005) and date2 (ex.14/06/2005). The code should find which
of those days are available (table contains something else than 0).

Following picture's first line shows a case where all those days are
available(0)in table. Def-field contains autonumber.

def 12 13 14
1 0 0 0
2 1 45 0

Now I want to run simple query which returns "def" field's value to
list-control "List" in the form named "Form1" if all the days contains "0".
So in this case the list would contain 1 (see picture). The query has to be
run through VBA, because the table and field names in query cannot be fixed.

I'm not sure if it was clear enough, but you are free to ask more information.

 

[Guest]

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
..rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect
'Stop

Me.list.RowSource = strSqlSelect
Me.list.Requery

End Sub


Also Note: Here is an example of how to call it.

Private Sub TestLoadListBox()

Call LoadListBox("tblMonth2", , , , , , , , , , , , True, True, True)

End Sub

This will set the forms listbox named "list", rowsource to show all of the
defs (Autonumbers) from that table where fields named 11, 12, 13 are equal to
0 (zero) now if you are looking at null values or some sort of text values
other than zeros you will need to change the code accordingly. I know it
isn't pretty but neither is this design! LOL... Take Care & God Bless -- ~
SPARKER ~

I have form which searches for available days from db. Db contains tables for
6 month with fields named 1-31(or 30 depends on month). In the form I put
date1 (ex. 12/06/2005) and date2 (ex.14/06/2005). The code should find which
of those days are available (table contains something else than 0).

Following picture's first line shows a case where all those days are
available(0)in table. Def-field contains autonumber.

def 12 13 14
1 0 0 0
2 1 45 0

Now I want to run simple query which returns "def" field's value to
list-control "List" in the form named "Form1" if all the days contains "0".
So in this case the list would contain 1 (see picture). The query has to be
run through VBA, because the table and field names in query cannot be fixed.

I'm not sure if it was clear enough, but you are free to ask more information.

 

[Guest]

Actually I didn't have doubts that your code is not working. But I ran the
test and the query works well.

Questions/answers

"The dates... Are these specific dates to be searched or is this a date
range?

The dates are actually defined by user, so they can be what ever. For
example start:23.7.2005 stop:13.8.2005

"Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other?"

All the months has their own table, so there are six tables (half a year).

"If that is the case then the field "def" is
actualy the number of the month?"

Def is a number of person. So one table contains for example "def" values
from 1-80. So it means that 80 persons month to be booked.

"Also it appears in your example that you have your date format as
"dd/mm/yyyy" is that correct?"

That is correct (dd/mm/yyyy).
--
-Sam

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
.rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect
'Stop

Me.list.RowSource = strSqlSelect
Me.list.Requery

End Sub


Also Note: Here is an example of how to call it.

Private Sub TestLoadListBox()

Call LoadListBox("tblMonth2", , , , , , , , , , , , True, True, True)

End Sub

This will set the forms listbox named "list", rowsource to show all of the
defs (Autonumbers) from that table where fields named 11, 12, 13 are equal to
0 (zero) now if you are looking at null values or some sort of text values
other than zeros you will need to change the code accordingly. I know it
isn't pretty but neither is this design! LOL... Take Care & God Bless -- ~
SPARKER ~


:

I have form which searches for available days from db. Db contains tables for
6 month with fields named 1-31(or 30 depends on month). In the form I put
date1 (ex. 12/06/2005) and date2 (ex.14/06/2005). The code should find which
of those days are available (table contains something else than 0).

Following picture's first line shows a case where all those days are
available(0)in table. Def-field contains autonumber.

def 12 13 14
1 0 0 0
2 1 45 0

Now I want to run simple query which returns "def" field's value to
list-control "List" in the form named "Form1" if all the days contains "0".
So in this case the list would contain 1 (see picture). The query has to be
run through VBA, because the table and field names in query cannot be fixed.

I'm not sure if it was clear enough, but you are free to ask more information.

 

[Guest]

Oh ok that's great! So you were able to get it to work? Did you want any help
with the user input? I am curious about the date "format" = "dd/mm/yyyy"
isn't that a little unusual?
--
~ SPARKER ~

Actually I didn't have doubts that your code is not working. But I ran the
test and the query works well.

Questions/answers

"The dates... Are these specific dates to be searched or is this a date
range?

The dates are actually defined by user, so they can be what ever. For
example start:23.7.2005 stop:13.8.2005

"Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other?"

All the months has their own table, so there are six tables (half a year).

"If that is the case then the field "def" is
actualy the number of the month?"

Def is a number of person. So one table contains for example "def" values
from 1-80. So it means that 80 persons month to be booked.

"Also it appears in your example that you have your date format as
"dd/mm/yyyy" is that correct?"

That is correct (dd/mm/yyyy).
--
-Sam

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
.rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam


:

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect
'Stop

Me.list.RowSource = strSqlSelect
Me.list.Requery

End Sub


Also Note: Here is an example of how to call it.

Private Sub TestLoadListBox()

Call LoadListBox("tblMonth2", , , , , , , , , , , , True, True, True)

End Sub

This will set the forms listbox named "list", rowsource to show all of the
defs (Autonumbers) from that table where fields named 11, 12, 13 are equal to
0 (zero) now if you are looking at null values or some sort of text values
other than zeros you will need to change the code accordingly. I know it
isn't pretty but neither is this design! LOL... Take Care & God Bless -- ~
SPARKER ~


:

 

[Guest]

Actually, when I separated it to simple query as you adviced me previously,
it worked, but I just cannot make it work with the "list". And I checked, the
"list" is "unbound". There must be something I'm missing here.

About the date. It might unusual. I cannot say, I have always done it with
this format.

You seem to be pretty expert with this thing. I don't have experiance with
these error handling situations. I assume there should be always so kind of
basic "if error goto" procedure. What is the basic lines for making this
thing?
--
-Sam


"sparker" kirjoitti:

Oh ok that's great! So you were able to get it to work? Did you want any help
with the user input? I am curious about the date "format" = "dd/mm/yyyy"
isn't that a little unusual?
--
~ SPARKER ~

Actually I didn't have doubts that your code is not working. But I ran the
test and the query works well.

Questions/answers

"The dates... Are these specific dates to be searched or is this a date
range?

The dates are actually defined by user, so they can be what ever. For
example start:23.7.2005 stop:13.8.2005

"Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other?"

All the months has their own table, so there are six tables (half a year).

"If that is the case then the field "def" is
actualy the number of the month?"

Def is a number of person. So one table contains for example "def" values
from 1-80. So it means that 80 persons month to be booked.

"Also it appears in your example that you have your date format as
"dd/mm/yyyy" is that correct?"

That is correct (dd/mm/yyyy).
--
-Sam

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
.rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~


:

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam


:

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect
'Stop

Me.list.RowSource = strSqlSelect
Me.list.Requery

End Sub


Also Note: Here is an example of how to call it.

Private Sub TestLoadListBox()

Call LoadListBox("tblMonth2", , , , , , , , , , , , True, True, True)

End Sub

This will set the forms listbox named "list", rowsource to show all of the

 

[Guest]

Sam,
is there any chance you can send me a small zip file with the stripped out
verion of this database as in just the tables and queries and form that this
list is requiring so i can actually look at it. There are a lot of people
around here that know more than me but thanks for the compliment. As far as
"Error Handling" it depends how in depth yuo want to get. You could so far as
to get all of the information possible and record it in a table like
tblErrors or you may just want the very basics... To get started you will
want to put at the top of every Sub or Function the basic
On Error GoTo HandleError and then at the bottom of your code you will want
to place an exit and then the actual code to handle the error... Like this:

Private Sub MySubOrFunctionName()

On Error GoTo MySubOrFunctionName_Error

<<<<All of your sub or function code here>>>>

MySubOrFunctionName_Exit:
Exit Sub

MySubOrFunctionName_Error:
MsgBox "An error has occured:" & vbcrlf & vbcrlf & _
"Error # " & Err.number & vbcrlf & vbcrlf & _
"Error Description:" & vbcrlf & _
"" Err.Description & vbcrlf & vbcrlf & _
"Sub or Function Name:" & vbcrlf &
"Mod or Form Name:" & vbcrlf &
"Database Name:" & vbcrlf &
"User Name:" & vbcrlf &
"Date & Time Stamp:",vbInformation,"Error Information"

Resume MySubOrFunctionName_Exit

End Sub


So you can use that in all of your subs or functions and get as detailed as
you want to get. And like I said you may want to record these errors in a
table... I do not know if you are using SQL Server but if you are there is
additional things you can do to handle any errors returned from the Server...
Hope this helps... I am assuming that you realize you would need to pass in
some of the information to this code such as the module name, sub name,
datetime, user, etc... If you need help with that just holler... Take Care &
God Bless ~ SPARKER ~

Actually, when I separated it to simple query as you adviced me previously,
it worked, but I just cannot make it work with the "list". And I checked, the
"list" is "unbound". There must be something I'm missing here.

About the date. It might unusual. I cannot say, I have always done it with
this format.

You seem to be pretty expert with this thing. I don't have experiance with
these error handling situations. I assume there should be always so kind of
basic "if error goto" procedure. What is the basic lines for making this
thing?
--
-Sam


"sparker" kirjoitti:

Oh ok that's great! So you were able to get it to work? Did you want any help
with the user input? I am curious about the date "format" = "dd/mm/yyyy"
isn't that a little unusual?
--
~ SPARKER ~

Actually I didn't have doubts that your code is not working. But I ran the
test and the query works well.

Questions/answers

"The dates... Are these specific dates to be searched or is this a date
range?

The dates are actually defined by user, so they can be what ever. For
example start:23.7.2005 stop:13.8.2005

"Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other?"

All the months has their own table, so there are six tables (half a year).

"If that is the case then the field "def" is
actualy the number of the month?"

Def is a number of person. So one table contains for example "def" values
from 1-80. So it means that 80 persons month to be booked.

"Also it appears in your example that you have your date format as
"dd/mm/yyyy" is that correct?"

That is correct (dd/mm/yyyy).
--
-Sam


:

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
.rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~


:

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam


:

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

If blnDay23 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[23])= 0) "
End If

If blnDay24 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[24])= 0) "
End If

If blnDay25 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[25])= 0) "
End If

If blnDay26 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[26])= 0) "
End If

If blnDay27 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[27])= 0) "
End If

If blnDay28 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[28])= 0) "
End If

If blnDay29 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[29])= 0) "
End If

If blnDay30 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[30])= 0) "
End If

If blnDay31 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[31])= 0) "
End If

strSqlSelect = strSqlSelect & ");"

'Debug.Print strSqlSelect

 

[Guest]

Hello Sparker. For give me, I have been out of this programming stuff for a
while. Thanks for the advices again. Yes I think those basic error handlings
are enough for this project. Anyway the application is to be used with people
who cannot solve the occured problems by themself, they don't know about
computers too much?

I'm still little bit busy and cannot do so much with Access at the moment,
but I will see what I can do with the stripped version of my project. I think
it would be very helpful for you to look at it.
--
-Sam


"sparker" kirjoitti:

Sam,
is there any chance you can send me a small zip file with the stripped out
verion of this database as in just the tables and queries and form that this
list is requiring so i can actually look at it. There are a lot of people
around here that know more than me but thanks for the compliment. As far as
"Error Handling" it depends how in depth yuo want to get. You could so far as
to get all of the information possible and record it in a table like
tblErrors or you may just want the very basics... To get started you will
want to put at the top of every Sub or Function the basic
On Error GoTo HandleError and then at the bottom of your code you will want
to place an exit and then the actual code to handle the error... Like this:

Private Sub MySubOrFunctionName()

On Error GoTo MySubOrFunctionName_Error

<<<<All of your sub or function code here>>>>

MySubOrFunctionName_Exit:
Exit Sub

MySubOrFunctionName_Error:
MsgBox "An error has occured:" & vbcrlf & vbcrlf & _
"Error # " & Err.number & vbcrlf & vbcrlf & _
"Error Description:" & vbcrlf & _
"" Err.Description & vbcrlf & vbcrlf & _
"Sub or Function Name:" & vbcrlf &
"Mod or Form Name:" & vbcrlf &
"Database Name:" & vbcrlf &
"User Name:" & vbcrlf &
"Date & Time Stamp:",vbInformation,"Error Information"

Resume MySubOrFunctionName_Exit

End Sub


So you can use that in all of your subs or functions and get as detailed as
you want to get. And like I said you may want to record these errors in a
table... I do not know if you are using SQL Server but if you are there is
additional things you can do to handle any errors returned from the Server...
Hope this helps... I am assuming that you realize you would need to pass in
some of the information to this code such as the module name, sub name,
datetime, user, etc... If you need help with that just holler... Take Care &
God Bless ~ SPARKER ~

Actually, when I separated it to simple query as you adviced me previously,
it worked, but I just cannot make it work with the "list". And I checked, the
"list" is "unbound". There must be something I'm missing here.

About the date. It might unusual. I cannot say, I have always done it with
this format.

You seem to be pretty expert with this thing. I don't have experiance with
these error handling situations. I assume there should be always so kind of
basic "if error goto" procedure. What is the basic lines for making this
thing?
--
-Sam


"sparker" kirjoitti:

Oh ok that's great! So you were able to get it to work? Did you want any help
with the user input? I am curious about the date "format" = "dd/mm/yyyy"
isn't that a little unusual?
--
~ SPARKER ~


:

Actually I didn't have doubts that your code is not working. But I ran the
test and the query works well.

Questions/answers

"The dates... Are these specific dates to be searched or is this a date
range?

The dates are actually defined by user, so they can be what ever. For
example start:23.7.2005 stop:13.8.2005

"Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other?"

All the months has their own table, so there are six tables (half a year).

"If that is the case then the field "def" is
actualy the number of the month?"

Def is a number of person. So one table contains for example "def" values
from 1-80. So it means that 80 persons month to be booked.

"Also it appears in your example that you have your date format as
"dd/mm/yyyy" is that correct?"

That is correct (dd/mm/yyyy).
--
-Sam


:

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
.rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~


:

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam


:

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If

If blnDay20 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[20])= 0) "
End If

If blnDay21 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[21])= 0) "
End If

If blnDay22 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[22])= 0) "
End If

 

[Guest]

Sam,
whenever you get around to it my email address is inbetween the
Spamfree@ and the @Spamfree here:

Spamfree@[email protected]@Spamfree

Take Care & God Bless ~ SPARKER ~

Hello Sparker. For give me, I have been out of this programming stuff for a
while. Thanks for the advices again. Yes I think those basic error handlings
are enough for this project. Anyway the application is to be used with people
who cannot solve the occured problems by themself, they don't know about
computers too much?

I'm still little bit busy and cannot do so much with Access at the moment,
but I will see what I can do with the stripped version of my project. I think
it would be very helpful for you to look at it.
--
-Sam


"sparker" kirjoitti:

Sam,
is there any chance you can send me a small zip file with the stripped out
verion of this database as in just the tables and queries and form that this
list is requiring so i can actually look at it. There are a lot of people
around here that know more than me but thanks for the compliment. As far as
"Error Handling" it depends how in depth yuo want to get. You could so far as
to get all of the information possible and record it in a table like
tblErrors or you may just want the very basics... To get started you will
want to put at the top of every Sub or Function the basic
On Error GoTo HandleError and then at the bottom of your code you will want
to place an exit and then the actual code to handle the error... Like this:

Private Sub MySubOrFunctionName()

On Error GoTo MySubOrFunctionName_Error

<<<<All of your sub or function code here>>>>

MySubOrFunctionName_Exit:
Exit Sub

MySubOrFunctionName_Error:
MsgBox "An error has occured:" & vbcrlf & vbcrlf & _
"Error # " & Err.number & vbcrlf & vbcrlf & _
"Error Description:" & vbcrlf & _
"" Err.Description & vbcrlf & vbcrlf & _
"Sub or Function Name:" & vbcrlf &
"Mod or Form Name:" & vbcrlf &
"Database Name:" & vbcrlf &
"User Name:" & vbcrlf &
"Date & Time Stamp:",vbInformation,"Error Information"

Resume MySubOrFunctionName_Exit

End Sub


So you can use that in all of your subs or functions and get as detailed as
you want to get. And like I said you may want to record these errors in a
table... I do not know if you are using SQL Server but if you are there is
additional things you can do to handle any errors returned from the Server...
Hope this helps... I am assuming that you realize you would need to pass in
some of the information to this code such as the module name, sub name,
datetime, user, etc... If you need help with that just holler... Take Care &
God Bless ~ SPARKER ~

Actually, when I separated it to simple query as you adviced me previously,
it worked, but I just cannot make it work with the "list". And I checked, the
"list" is "unbound". There must be something I'm missing here.

About the date. It might unusual. I cannot say, I have always done it with
this format.

You seem to be pretty expert with this thing. I don't have experiance with
these error handling situations. I assume there should be always so kind of
basic "if error goto" procedure. What is the basic lines for making this
thing?
--
-Sam


"sparker" kirjoitti:

Oh ok that's great! So you were able to get it to work? Did you want any help
with the user input? I am curious about the date "format" = "dd/mm/yyyy"
isn't that a little unusual?
--
~ SPARKER ~


:

Actually I didn't have doubts that your code is not working. But I ran the
test and the query works well.

Questions/answers

"The dates... Are these specific dates to be searched or is this a date
range?

The dates are actually defined by user, so they can be what ever. For
example start:23.7.2005 stop:13.8.2005

"Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other?"

All the months has their own table, so there are six tables (half a year).

"If that is the case then the field "def" is
actualy the number of the month?"

Def is a number of person. So one table contains for example "def" values
from 1-80. So it means that 80 persons month to be booked.

"Also it appears in your example that you have your date format as
"dd/mm/yyyy" is that correct?"

That is correct (dd/mm/yyyy).
--
-Sam


:

Sam,
I have tested this code when I wrote it and when it executes it does return
the def values. This leads me to believe that the object itself is not ready
to handle the code. I have to wonder if you have this control bound to a
table or query. In order for this code to work you will need a list box named
"list" that is unbound. I can add some more code to help support the user
input if I understood what exactly is taking place here. As in... The
dates... Are these specific dates to be searched or is this a date range? As
in a start date and stop date? Also if I am understanding you correctly, are
there just the two tables with 6 months in each? January - June in table1 and
July - December in the other? If that is the case then the field "def" is
actualy the number of the month? I can give you a more complete solution but
I would need some answers to these questions. Also it appears in your example
that you have your date format as "dd/mm/yyyy" is that correct? Anyway in
short this code has been tested and is functional within the confines of
Access 2003 and I am confident beyond a shadow of a doubt it will function in
2000 or even 2005 versions... I must believe there is something about the
object itself "The ListBox" that is not prepared to handle the new
rowsource... I.E. it is bound or something... Other than that I can't imagine
the problem.... So my advice would be to make sure it is unbound and recheck
it. You can uncomment the debug and the stop lines in the code and then the
strSqlSelect will be printed in your immediate window. You can open that by
hitting ( CTRL + G ) or else going to the view drop down at the top menu bar
and selecting Immediate Window. Now when you execute the code it will print
the Sql Code to that window where you can copy and past it into the shell of
a brand new query and see the results. As well as copy and paste it into the
.rowsource of your list box and see the def values. This should prove to you
that the code does function. If you would like some more help with this
please advise. P.S. I think we could make it a smaller Sub if I had more
input as to what is going on... Take Care & God Bless-- ~ SPARKER ~


:

First of all I want to thank you for giving time to create this "monster
code". I tried and the basic plan is pretty nice. How ever now it gives the
whole SQL procedure as a code-format in the "List", when it should give what
def-field contains.
--
-Sam


:

Greetings Sam!
Here is a solution that will work when passing the table name followed by a
series of commas as place holders inbetween the desired field names you wish
to search. In other words if you only want to look at day 31 of table1 then
you would need to pass the 30 commas as place holders between table1 and
true. there is an example at the bottom on how to call this. You can copy and
paste this directly to your forms module. You should be able to take the user
input and pass it to this sub. Enjoy!

Private Sub LoadListBox(strTableName As String, _
Optional blnDay1 As Boolean = False, Optional blnDay2 As Boolean = False,
Optional blnDay3 As Boolean = False, _
Optional blnDay4 As Boolean = False, Optional blnDay5 As Boolean = False,
Optional blnDay6 As Boolean = False, _
Optional blnDay7 As Boolean = False, Optional blnDay8 As Boolean = False,
Optional blnDay9 As Boolean = False, _
Optional blnDay10 As Boolean = False, Optional blnDay11 As Boolean = False,
Optional blnDay12 As Boolean = False, _
Optional blnDay13 As Boolean = False, Optional blnDay14 As Boolean = False,
Optional blnDay15 As Boolean = False, _
Optional blnDay16 As Boolean = False, Optional blnDay17 As Boolean = False,
Optional blnDay18 As Boolean = False, _
Optional blnDay19 As Boolean = False, Optional blnDay20 As Boolean = False,
Optional blnDay21 As Boolean = False, _
Optional blnDay22 As Boolean = False, Optional blnDay23 As Boolean = False,
Optional blnDay24 As Boolean = False, _
Optional blnDay25 As Boolean = False, Optional blnDay26 As Boolean = False,
Optional blnDay27 As Boolean = False, _
Optional blnDay28 As Boolean = False, Optional blnDay29 As Boolean = False,
Optional blnDay30 As Boolean = False, _
Optional blnDay31 As Boolean = False)

Dim strSqlSelect As String

strSqlSelect = _
"SELECT " & strTableName & ".def " & _
"FROM " & strTableName & " " & _
"WHERE (((" & strTableName & ".def) Is Not Null) "

If blnDay1 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[1])= 0) "
End If

If blnDay2 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[2])= 0) "
End If

If blnDay3 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[3])= 0) "
End If

If blnDay4 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[4])= 0) "
End If

If blnDay5 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[5])= 0) "
End If

If blnDay6 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[6])= 0) "
End If

If blnDay7 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[7])= 0) "
End If

If blnDay8 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[8])= 0) "
End If

If blnDay9 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[9])= 0) "
End If

If blnDay10 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[10])= 0) "
End If

If blnDay11 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[11])= 0) "
End If

If blnDay12 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[12])= 0) "
End If

If blnDay13 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[13])= 0) "
End If

If blnDay14 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[14])= 0) "
End If

If blnDay15 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[15])= 0) "
End If

If blnDay16 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[16])= 0) "
End If

If blnDay17 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[17])= 0) "
End If

If blnDay18 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[18])= 0) "
End If

If blnDay19 = True Then
strSqlSelect = strSqlSelect & _
"AND ((" & strTableName & ".[19])= 0) "
End If