Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

RE: filtering and operator codes.

Newbie
  
Join Date: Oct 2009
Posts: 2
#1: Oct 21 '09
Hi guys,

I am currently doing this loaning system for a school, using microsoft access. I had this problem with a filter code. When I click it, it can't run there is a runtime error '3079'.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2. If IsNull(Me.Text78) Then
  3. Me.FilterOn = False
  4. Else
  5. Me.Filter = "DATEOFLOAN = """ & Me.Text78 & """"
  6. Me.FilterOn = True
  7. MsgBox "Filter applied"
  8. End If
  9. End Sub
The DATEOFLOAN is a field where user will loan stuff and the date will be recorded. the text78 will be the place where I select the date to filter.

Please help.

I got any coding also which can't work but revolve around the same thing. Because previously my DATEOFLOAN field is actually 'DATE OF LOAN' with spaces in between so I use this code.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2. If IsNull(Me.Text78) Then
  3. Me.FilterOn = False
  4. Else
  5. Me.Filter = "DATE_OF_LOAN = """ & Me.Text78 & """"
  6. Me.FilterOn = True
  7. MsgBox "Filter applied"
  8. End If
  9. End Sub
The program can't seems to recognize my DATE OF LOAN same for (DATE_OF_LOAN), 'DATE_OF_LOAN', Please help.

I read VBA books for dummies(Basics) spend around 10-20 hours online searching but I can not find a link of a single resources that explain to me how to use the colon and semi colon or the """ & Me.Text78 & """. How is the & sign needed.

I really want to learn how these operator work. So if you guys can point me to the right resources I would really appriciated it.
best answer - posted by NeoPa
Quote:

Originally Posted by Danielker View Post

Hi guys,

I am currently doing this loaning system for a school, using microsoft access. I had this problem with a filter code. When I click it, it can't run there is a runtime error '3079'.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2. If IsNull(Me.Text78) Then
  3. Me.FilterOn = False
  4. Else
  5. Me.Filter = "DATEOFLOAN = """ & Me.Text78 & """"
  6. Me.FilterOn = True
  7. MsgBox "Filter applied"
  8. End If
  9. End Sub

Firstly, when posting code it is mandatory to use the CODE tags (I know - first post & all - not a problem - but try to remember for future posts).

It is a good idea also to post error messages and the line # that they are triggered on.

In this case I would assume (refer to line #5) that DATEOFLOAN is actually a Date/Time field so should be something like :
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[DATEOFLOAN]=" & Format(CDate(Me.Text78),'\#m/d/yyyy\#')
Your whole procedure could be tidied up as :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2.     With Me
  3.         .FilterOn = (Not IsNull(.Text78))
  4.         If .FilterOn Then
  5.             .Filter = "[DATEOFLOAN]=" & Format(CDate(Me.Text78),'\#m/d/yyyy\#')
  6.             MsgBox "Filter applied"
  7.         End If
  8.     End With
  9. End Sub
You could even use (IsDate()) on line #3 instead, or use it to handle invalid data.

NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,765
#2: Oct 21 '09

re: RE: filtering and operator codes.

Some links to start with.
Quotes (') and Double-Quotes (") - Where and When to use them.
Using "&" and "+" in WHERE Clause.
Literal DateTimes and Their Delimiters (#).

I hope this is some help to be starting with.

Welcome to Bytes!
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,765
#3: Oct 21 '09

re: RE: filtering and operator codes.

Quote:

Originally Posted by Danielker View Post

Hi guys,

I am currently doing this loaning system for a school, using microsoft access. I had this problem with a filter code. When I click it, it can't run there is a runtime error '3079'.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2. If IsNull(Me.Text78) Then
  3. Me.FilterOn = False
  4. Else
  5. Me.Filter = "DATEOFLOAN = """ & Me.Text78 & """"
  6. Me.FilterOn = True
  7. MsgBox "Filter applied"
  8. End If
  9. End Sub

Firstly, when posting code it is mandatory to use the CODE tags (I know - first post & all - not a problem - but try to remember for future posts).

It is a good idea also to post error messages and the line # that they are triggered on.

In this case I would assume (refer to line #5) that DATEOFLOAN is actually a Date/Time field so should be something like :
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[DATEOFLOAN]=" & Format(CDate(Me.Text78),'\#m/d/yyyy\#')
Your whole procedure could be tidied up as :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2.     With Me
  3.         .FilterOn = (Not IsNull(.Text78))
  4.         If .FilterOn Then
  5.             .Filter = "[DATEOFLOAN]=" & Format(CDate(Me.Text78),'\#m/d/yyyy\#')
  6.             MsgBox "Filter applied"
  7.         End If
  8.     End With
  9. End Sub
You could even use (IsDate()) on line #3 instead, or use it to handle invalid data.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,765
#4: Oct 21 '09

re: RE: filtering and operator codes.

Quote:

Originally Posted by Danielker View Post

I got any coding also which can't work but revolve around the same thing. Because previously my DATEOFLOAN field is actually 'DATE OF LOAN' with spaces in between so I use this code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command82_Click()
  2. If IsNull(Me.Text78) Then
  3. Me.FilterOn = False
  4. Else
  5. Me.Filter = "DATE_OF_LOAN = """ & Me.Text78 & """"
  6. Me.FilterOn = True
  7. MsgBox "Filter applied"
  8. End If
  9. End Sub
The program can't seems to recognize my DATE OF LOAN same for (DATE_OF_LOAN), 'DATE_OF_LOAN', Please help.

I read VBA books for dummies(Basics) spend around 10-20 hours online searching but I can not find a link of a single resources that explain to me how to use the colon and semi colon or the """ & Me.Text78 & """. How is the & sign needed.

I really want to learn how these operator work. So if you guys can point me to the right resources I would really appriciated it.

It's wise to avoid using embedded spaces in names (full stop), but if you must then simply surround the name with brackets for it to be recognised perfectly well.
EG. [DATE OF LOAN].

An alternative might be DateOfLoan. Clear, but without the complication.
Newbie
  
Join Date: Oct 2009
Posts: 2
#5: Oct 22 '09

re: RE: filtering and operator codes.

Yes thanks alot! I am very new to VBA coding and we need to create a program for an asset loaning system. I realise now that making spaces in names for object is a rookie mistake. Thanks for pointing it out.

The resources you provide is really very useful in my first step to understanding quotes and all. I will be trying the codes tonight.

Thanks alot!!
Daniel.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,765
#6: Oct 22 '09

re: RE: filtering and operator codes.

You're welcome Daniel. It's always a good sign I find when members ask for help to learn things themselves.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,567 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved